COUNTA formula with indirect reference - help please!

[Jason Lang]

Hi--

I am getting strange results with a formula. I would like to use
COUNTA to count non blanks in a worksheet called "Data". I'd like it
to take column values from cells C2 and D2 to get the range of data in
"Data" to count. What I have is:

=COUNTA("Data!"&$C$2&ROW()&"ata!"&$D$2&ROW())

However, this always returns 1 regardless of the number of blanks.

I think my problem is related to the following. Just playing around,
I tried a direct formula to count nonblanks in columns AE:AJ:

=COUNTA(Data!AE3ata!AJ3) --- Works correctly
=COUNTA("Data!AE3ata!AJ3") --- always returns 1 like my example
above

Any suggestions for how to get the first formula to work correctly?

Thanks!

 

[Dave Peterson]

Maybe...
=COUNTA(INDIRECT("Data!"&$C$2&ROW()&":"&$D$2&ROW()))

Your formula which boils down to this:
=COUNTA("Data!AE3ata!AJ3")

Is counting how many things are inside those (). In this case you have a single
string, so you get 1.

=counta("a","b")
would return 2

=COUNTA(Data!AE3ata!AJ3)
More often written like:
=COUNTA(Data!AE3:AJ3)
will count the non-empty cells in AE3:AJ3 of the Data worksheet.

 

[Pete_UK]

You can build up a cell or range address as a string (as you are
trying to do), but you must use the INDIRECT function to get Excel to
recognise it. Try this:

=COUNTA(INDIRECT("Data!"&$C$2&ROW()&"ata!"&$D$2&ROW()))

Hope this helps.

Pete

 

[T. Valko]

Try it like this:

=COUNTA(INDIRECT("Data!"&$C$2&ROW()&":"&$D$2&ROW()))

 

[Jason Lang]

Hi all--
Thanks so much for the replies. Unfortunately, all of these formulas
continue to give me the value of "1" when I put them in my
spreadsheet. Any other ideas?

Jason

 

[T. Valko]

See this screencap:

http://img142.imageshack.us/img142/6184/indirectej3.jpg

The formula does work. I just used it on the same sheet so that you can see
it does return the correct results.

 

[Jason Lang]

You are right...I am not sure what I did wrong before. Thanks!!

 

[T. Valko]

Getting all of those "stupid" quotes in the correct order can be a challenge
sometimes! That's what usually trips people up.

Thanks for feeding back!