Please help with countif formula
 

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.

Please help with countif formula
 

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.

Please help with countif formula
 

=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.

Please help with countif formula
 

Micky,

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

"מיכאל (מיקי) אבידן" wrote:

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.

Please help with countif formula
 

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

--
Jacob


"Accesshelp" wrote:

Micky,

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

"מיכאל (מיקי) אבידן" wrote:

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.