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!

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.



Jason Lang wrote:
>
> 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

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

On Mar 4, 10:00*pm, Jason Lang <jal...@uchc.edu> wrote:
> 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!

Try it like this:

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


--
Biff
Microsoft Excel MVP


"Jason Lang" <(E-Mail Removed)> wrote in message
news:fd4162f0-7622-4d5b-853c-(E-Mail Removed)...
> 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!

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

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.


--
Biff
Microsoft Excel MVP


"Jason Lang" <(E-Mail Removed)> wrote in message
news:458599ed-cf72-4692-97dd-(E-Mail Removed)...
> 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

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

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!

--
Biff
Microsoft Excel MVP


"Jason Lang" <(E-Mail Removed)> wrote in message
news:9c164100-9943-4cc6-899a-(E-Mail Removed)...
> You are right...I am not sure what I did wrong before. Thanks!!
>