I have a spreadsheet that I need to count entries made in a column. So cell A1 has ‘=counta(A2:A1000)’ and cell B1 has ‘=counta(B2:B1000)’ ect. What I would like to do is use another cell, say Z1, as a varible to define the starting row of the count. So it would sorta do ‘=counta(A{Z1}:A1000)’ and ‘=counta(B{Z1}:B1000) Anyone got some advice on how to achieve this?

Stealth, I’m on phone at the moment and don’t have Excel but you could try the offset command.

Something like =Counta(offset(B2,Z1,1,1000,1))

I think that will work. Or lookup offset command.

All the best with it.

=COUNTA:A1000)

To see that formula, unmolested by forum software, select quote from my previous post…

Maybe this will work:

=COUNTA(INDIRECT("A"&Z1):A1000)

furious said:

Maybe this will work:

=COUNTA(INDIRECT("A"&Z1):A1000)

Thanks, this works a treat (except if you put a 1 into Z1 and create a circular reference…)

Next Question:
When I click and drag the formula from A1 to B1, C1… the “A” remains A and the Z1 becomes AA1, AB1… and the A1000 becomes B1000, C1000… Only the third lookup is changing to what is required. Any hints on making the first two drag correctly.

put $ in front of whatever you want to stay static.
cell A1 therefore becomes $A$1.

Damn it Stan, that drag issue has given me the bejeebez for years, and it is such a simple fix. Thank you very much.

no problems. ;)

And thanks to Furious for the indirect function. Have never used it. Will come in very helpful in future.