Need Help with Countif

[pkohli]

Hi

I need a help with countif function. I am trying to write a formula :-

Book1
	A	B	C	D
1	A	10
2	B
3	A
4	C	2
5	A	3
6	B	4
7	C	67
8	C	8
9	B	9
10
11	A	2
12	B	2
13	C	3
Sheet1

The formula I am trying to write is if column A has "A" count the values for A in column B...if there is no value then it shld count it as zero..

Can someone please help.

 

[hatman]

perhaps

=sumproduct(--(A:A="A"),--(B:B<>0))

 

[pkohli]

Not working...I need to count not sum..also could some one tell me how to use countif with 2 criteria i.e. -

Book2
	A	B	C	D
1	Name	Address	Status
2	Ben	NA	H
3	Ricky	NA	H
4	Lee	UK	M
5	Lee	UK	M
6	Foe	AU	L
7	Ricky	AU	L
8	Ricky	UK	H
9	Ricky	US	L
10	Ben	UK	M
11	Ben	UK	H
12	Ben	AU	M
13	Ben	AU	L
Sheet1

I want to count if name is ben and address is UK, how many H are in status.

 

[barry houdini]

You can't use whole columns for SUMPRODUCT but it should work if you use

=SUMPRODUCT(--(A2:A100="ben"),--(B2:B100="uk"),--(C2:C100="h"))

 

[hatman]

=sumproduct(--(a1:a100="Ben"),--(b1:b100="UK"),--(c1:c100="H"))

 

[hatman]

You can't use whole columns for SUMPRODUCT but it should work if you use

=SUMPRODUCT(--(A2:A100="ben"),--(B2:B100="uk"),--(C2:C100="h"))

oops forgot

 

[pkohli]

What if I want to use whole number also?? As mentioned in the previous post??

 

[barry houdini]

What exactly are the criteria? column B needs to be any number?

Perhaps try

=SUMPRODUCT(--(A1:A100="a"),--ISNUMBER(B1:B100))