How to Count with 2 conditions?

**Lewis Koh** · 04-18-2010, 04:50 AM

Hi,

Now I got another problem...

A1 = Apple
A2 = Pear
A3 = Orange
A4 = Starfruit
A5 = Apple
A6= Apple

B1 = 1
B2 = 1
B3 = 1
B4 = 0
B5 = 0
B6= 1

I want to count the apples but second condition is that B1:B6 must be greater than 0.

I search example for COUNTIF but it only has COUNTIF(range,criteria)
I tried =COUNTIF(A1:A5,AND("APPLE",B1:B5>0)) but it gives me the wrong count.

**contaminated** · 04-18-2010, 04:56 AM

try
=SUMPRODUCT((A1:A6="Apple")*(B1:B6>0))

**royUK** · 04-18-2010, 04:56 AM

Is this the same as your previous question?

**Lewis Koh** · 04-18-2010, 05:04 AM

Originally Posted by royUK

Is this the same as your previous question?

Hi Roy, nope, it's different. I mean the question was the same as the one you asked me to post as a new thread but the function is different.

Hi contaminated, the count seems to be wrong. Let me try again later. I got to go away for a while.

**Lewis Koh** · 04-18-2010, 08:58 AM

Originally Posted by contaminated

try
=SUMPRODUCT((A1:A6="Apple")*(B1:B6>0))

Hi contaminated! It works!!!! Thanks.

**zbor** · 04-18-2010, 09:00 AM

Originally Posted by contaminated

try
=SUMPRODUCT((A1:A6="Apple")*(B1:B6>0))

If B1:B6 can't be negative then =SUMPRODUCT(--(A1:A6="Apple");B1:B6) is enough

**Lewis Koh** · 04-21-2010, 11:48 AM

Originally Posted by contaminated

try
=SUMPRODUCT((A1:A6="Apple")*(B1:B6>0))

Hi,

Somehow there is a different column which gave 0 count. Not sure why.

=SUMPRODUCT((Q11:Q83="Orange")*(Z11:Z83>0))

Number of oranges are counted correctly but when I do SUMPRODUCT, it gave me 0. Did I do something wrong or change something without realizing it?

If I want to count Q11:Q83="Orange" and Z11:Z83="1", can I still use SUMPRODUCT?

Hi Guys, I just found out I need to put a " ' " in front of 1 for the SUMPRODUCT to count. Why is that so? Are there any other characters that will prevent counting?