Please help with countif formula

[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

 

[Bernard Liengme]

=SUMPRODUCT(--(A1:A100="John"), --(B1:B100<> ""))
or
=SUMPRODUCT((A1:A100="John")*(B1:B100<> ""))

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctions04.html#SumProduct


If you are using Excel 2007, read Help on COUNTIFS (note final S) and come
back if more help needed
best wishes

 

[Accesshelp]

Micky,

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

 

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