=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/xlFunctio...tml#SumProduct


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

--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP



wrote in message
...
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.