cell reference in formula

[Michael Dwyer]

This is going to be very simple, I just know it, but I am stuck in a
mind-trap

A working formula which returns a count:
"=COUNTA(2:2)"
This would return something like "27", a count of cells in row 2 with
data, as does"
"=COUNTA($2:$2)"
"=COUNTA(MySheet!$2:MySheet!$2)"

Now I want to have a cell which I enter a "row number" for my formula
to count, i.e 2,3,4, etc

I would like to have something like
"=COUNTA((B5)B5))"
"=COUNTA($(B5):$(B5))"
"=COUNTA(MySheet!$(B5):MySheet!$(B5))"

..... work, but it does not, and I can see why is does not. I just can't
figure out how to make it work.

Like I said this should be simple, I'm just stuck at the moment

 

[Frank Kabel]

Hi
try
=COUNTA(INDIRECT(B5 & ":" & B5))

 

[Guest]

One way

=COUNTA(OFFSET(A1,B5-1,,,256))

You can also use INDIRECT

Regards,

Peo Sjoblom

 

[Bernd Plumhoff]

If you enter your row number in cell B1, then

=COUNTA(INDEX(1:65536,B1,1):INDEX(1:65536,B1,256))

will give you the intended result. This solution avoids
the INDIRECT() and the OFFSET() functions which are
volatile (see
http://www.whooper.co.uk/excelstuff.htm#tip1
).

HTH,
Bernd

 

[Harlan Grove]

Bernd Plumhoff wrote...

If you enter your row number in cell B1, then

=COUNTA(INDEX(1:65536,B1,1):INDEX(1:65536,B1,256))

will give you the intended result. This solution avoids the INDIRECT()
and the OFFSET() functions which are volatile . . .

....

Good point, but why use two INDEX calls? Why not

=COUNTA(INDEX(1:65536,B1,0))

?

 

[Bernd Plumhoff]

Hi Harlan,

Better point. Thanks.

Regards,
Bernd