Help with CountIF / SUM

[pete212]

Hello,

I'm not sure if this is in the right forum, apologies if not. I couldn'
see a general formular help section.

I have some data and need to do a COUNTIF but based on two seperat
conditions. I need it to count items only if there is a "3" in column
AND a "7200" in column B.

I've tried using the following:

COUNTIF(QBR!B:B,7200)+COUNTIF(QBR!J:J,3)

However this adds the total of "7200"s and the total of "3"s, regardles
if they 'match' in the same row.

I also tried:

COUNTIF(QBR!B:B,7200)AND*(QBR!J:J,3)

with no luck and:

SUMPRODUCT((QBR!B:B="7200")*(QBR!J:J="3"))

But this just returns 0, not sure why.

Does anyone have any ideas on how to get this working?

Many thanks for any help you can offer

 

[Claus Busch]

Hi Pete,

Am Wed, 9 May 2012 09:15:15 +0000 schrieb pete212:

I have some data and need to do a COUNTIF but based on two seperate
conditions. I need it to count items only if there is a "3" in column J
AND a "7200" in column B.

you are looking for numbers. Therefore you have to write the numbers
without quotes:
=SUMPRODUCT(--(QBR!B1:B100=7200),--(QBR!J1:J100=3))
or
=SUMPRODUCT((QBR!B1:B100=7200)*(QBR!J1:J100=3))
or if you have xl2007 or later:
=COUNTIFS(QBR!B:B,7200,QBR!J:J,3)


Regards
Claus Busch