MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Multiple criteria for Countif

Posted by Tom Boyd on January 07, 2002 2:23 PM

Hello All!

I am trying to chart a baseball schedule. How do I handle a countif with multiple criteria? For example:

1 05/30/02 Game1 Home Weekday
2 05/31/02 Game2 Away Night
3 06/01/02 Game3 Home Weekend
4 06/02/02 Game4 Away Weekend

=COUNTIF(B1:B4,"home") gives me 2
=COUNTIF(C1:C4,"weekend") gives me 2

How do I get a count of games that are both "home" AND "weekend"? BTW, my data ranges will be done by month.

Thanks in advance!


Posted by Scott on January 07, 2002 2:29 PM

Try this:

=(COUNTIF(B1:B4,"home")+ COUNTIF(C1:C4,"weekend"))

Posted by Aladin Akyurek on January 07, 2002 2:46 PM

Tom --


will you the count of game records for which both criteria/conditions simultaneously hold.

If you are interested in a count by month, enter month numbers 1 to 12 say in G from G2 on, and

in H2 enter: =SUMPRODUCT((MONTH($A$2:$A$10)=G2)*($C$2:$C$10="Home")*($D$2:$D$10="Weekend"))

Copy down this till to the row of the month number 12.

Note. I assumed that A2:D10 houses your sample data throughout the above.



Posted by tom boyd on January 07, 2002 2:47 PM

That was my first instinct too, but it gives the total number of home games PLUS the total number of weekend games. I need to get the number of games that are BOTH home and weekend, but countif only seems to allow one criteria...



Posted by tom boyd on January 07, 2002 2:51 PM

That's the one! Thanks!


Posted by Mark W. on January 07, 2002 2:53 PM

If Column B and C were the "Home/Away" and the
"Weekend/Weekday/Night" columns respectively...

=(COUNTIF(B1:B4,"home")+ COUNTIF(C1:C4,"weekend"))
would produce a count of 4 instead of the correct
value, 1.

The array formula, {=SUM((C1:C4="Home")*(D1:D4="Weekend"))},
is needed to produce the correct results.

Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.