Please help with countif formula

  • Thread starter Thread starter Accesshelp
  • Start date Start date
A

Accesshelp

Hello all,

I have 2 columns with data: A1:A100 and B1:B100. I need some help with a
formula something similar to countif. A1:A100 has names and B1:B100 has
numbers.

The formula that I need help with is If the cells in A1:A100 has the name
with "John" and the cells in B1:B100 with values, then give me the number
(count) of those cells.

So I try this formula:

{=count(if(and(a1:a100="John", b1:b100<>""),""))}

Somehow, that formula is not working. It keeps giving me the result with 1.

Please help. Thanks.
 
No need for Array Formula.
Try this one:
=SUMPRODUCT((A1:A100="John")*(B1:B100<>""))
Micky
 
Let us retry this

Col A Col B
John 2
Mary 0
John N/A
John
Jeff 1
John 2
Jim

In the above example if you expect the count all instances where ColB is not
blank try the formula Micky has suggested which should return 3.

Instead if you expect to count only values or numerics then try the below
formula which returns 2

=SUMPRODUCT((A1:A100="John")*(ISNUMBER(B1:B100)))
 
Back
Top