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:
A B C D
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!
-Tom
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 --
=SUMPRODUCT((C2:C10="Home")*(D2:D10="Weekend"))
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.
Aladin
========
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...
Thanks!
-Tom
Posted by tom boyd on January 07, 2002 2:51 PM
That's the one! Thanks!
-Tomy
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.
