Please help with countif formula

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
 
A

Accesshelp

Micky,

Thanks for the formula. I tried it, and somehow, it does not work. Thanks.
 
J

Jacob Skaria

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)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top