No need for Array Formula.
Try this one:
=SUMPRODUCT((A1:A100="John")*(B1:B100<""))
Micky


"Accesshelp" wrote:

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.