Counta problem - Wrong total

J

Jeremy

=COUNTA(D1:D22,"=A27")

In the above formula I believe I am counting the number of instances the
name in A27 appears in the list from D1 to D22.

In the actual list the name in A27 actually only appears once, but the
formula is telling me it appears 23 times.

I think I am not doing something right.

Please help
 
M

Mike H

Hi,

Your formula is counting non-blank cells in the range 'and' A27 hence the
answer 23

try this

=COUNTIF(D1:D22,"="&A27)

Mike
 
D

David Biddulph

You are correct. You are not doing something right.
Try looking up the function COUNTA in Excel help.
You may also wish to look up the function COUNTIF.
 
J

Jeremy

That worked beautifully, now if you coule teach me to fish for one second.

I get the fisrt part of the equation. It's the ,"="&A27 part that I don't
understand. What is this telling the computer to do?

Thanks, I want to learn this so one day I can help people too!
 
M

Mike H

Hi,

It tells excel to look at the address A27. If you put the entire thing in
quotes
"=A27" then excel treats that as a literal string instead of an address.

It's no different to using the concatenate sign (&) in a cell.

Mike
 
D

David Biddulph

You can get away with just =COUNTIF(D1:D22,A27)

As for learning to fish, as I suggested in another message on this thread,
use Excel help for the COUNTIF function.
 
Top