COUNTA doesnt' work with array formula (COUNT does!)

Question

Hi,

i'm trying to use the function COUNTA with an array formula (excel 2007), but it doesn't work.

It works with function COUNT, but not COUNTA!

I did this: to count the number of times Bob is in the table (A1:A4) here below, i did:

=COUNTA(IF(A1:A4="Bob";A1:A4)) but this returns always 4 instead of 2.

=COUNTA(IF(A1:A4="Bob";B1:B4)) returns also 4 instead of 2.

but =COUNT(IF(A1:A4="Bob";B1:B4)) returns 2!

What's wrong with using COUNTA?

Thanks

Bob

Bob	16
Vera	45
Philip	38
Bob	73

Answer 1

To answer your question of what's wrong with COUNTA consider:

=COUNTA(IF(A1:A4="Bob";A1:A4))

A1:A4="Bob" evaluates to {TRUE;FALSE;FALSE;TRUE}

IF(A1:A4="Bob";A1:A4)  evaluete to {"Bob";"";"";"Bob"}

COUNTA counts all nonblank entries and "" is not treated as blank here. 

If the range A2:A3 were blank the above results would still be 4!  However, in that case COUNTA(A1:A4) would return 2.  And if fact if all the cells were blank the array formula would return 4!?

So one would be justified in saying your logic is correct but Excel work's differently.

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire

Answer 2

Hi,

Your making life too hard for yourself and using the wrong fomula. Also you don't need an array formula to do what you want, this will suffice if all your doing is counting Bob in Col A

=COUNTIF(A1:A4,"Bob")

This formula also returns 2 but checks there is something in col B

=SUMPRODUCT((A1:A4="Bob")*(B1:B4<>""))

And to sum column B for every Bob in column A

=SUMPRODUCT((A1:A4="Bob")*(B1:B4))

What's wrong with using COUNTA?

Nothing, you were using it incorrectly

---

If this post answers your question, please mark it as the Answer.

Mike H

Answer 3

Thanks.

Answer 4

So one would be justified in saying your logic is correct but Excel work's differently.

Or as is sometimes wryly observed, "the customer is always right but not always correct" !

Regards,

Peter Thornton

Answer 5

CountA This will return the number of entries (actually counts each cell that contains number dataOR text data) in the selected range of cells.

• Blank entries are not Counted.
• Text entries ARE Counted

TRY =COUNTIF(A1:A4,"Bob")