COUNTIF - 2 Conditions

**tomlancaster** · 08-05-2009, 10:47 AM

I have a workbook with two sheets

One sheet has all the data on it

E.g.
Date, Time, Priority, Month

The other sheet is a summary sheet to show

How many tickets were raised in one month
And How many priority tickets were raised in one month (e.g. LOW, MEDIUM, HIGH)

I would like the formula to count how many HIGH priority tickets there were for the month of August, September, etc.

I am using this formula but its counting all the HIGH priority tickets:

=IF(COUNTIF('All Incidents'!$O:$O,"8"),COUNTIF('All Incidents'!$H:$H,"HIGH"),"0")

"8" being the month (August)

Am I going about this all wrong?

**sweep** · 08-05-2009, 11:00 AM

Hi,

Try this:

=SUMPRODUCT(('All Incidents'!$O1:$O10000,"8")*('All Incidents'!$H1:$H10000,"HIGH"))

**Palmetto** · 08-05-2009, 11:01 AM

Why not use a Pivot Table?

**tomlancaster** · 08-06-2009, 06:16 AM

Hi thanks for the formula - I tried it and it said "The formula you typed contains an error..."

Not sure on how to use pivot tables... Is that quicker and easier?

**DonkeyOte** · 08-06-2009, 06:19 AM

I think sweep was in COUNTIF mode when putting that together

should read:

=SUMPRODUCT(('All Incidents'!$O1:$O10000="8")*('All Incidents'!$H1:$H10000="HIGH"))
are you sure you need the 8 as a text string ie "8" and not 8 ?

Pivot Tables are easy to get to grips with and much better than lots of expensive formulae - ie if you intend to use the above frequently then use a pivot table - see link in my sig. for a general intro ...

**sweep** · 08-06-2009, 08:14 AM

Sweep was indeed in COUNTIF mode, and apologises for his foolishness.

**lanser** · 09-07-2010, 01:18 PM

sorry to reopen an old thread but I have a similar issue except I want to use a cell reference as the Month eg cell "A1" = January

=SUMPRODUCT(('All Incidents'!$O1:$O10000=A1)*('All Incidents'!$H1:$H10000="HIGH")) but I just get a value reading

**NBVC** · 09-07-2010, 01:23 PM

Welcome to the forum,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.