In an earlier post I demonstrated How to count cells with multiple criteria in Excel 2003. I used an Array Formula together with the SUM function.
Fortunately enough in Excel 20007 we do not need that workaround. Now there is a direct function that count cells with multiple criteria: the COUNTIFS function.
COUNTIFS(range1, criteria1,range2, criteria2…) … up to 127 range/criteria can be specified.
In the example below we want to count how often “John” appeared in the list in quarter 2 (Q2), with total sales that exceed 1000. i.e. we have three criteria:
- “John” in column B.
- “Q2” in column C.
- “>1000” in column D.
We will use the following formula for this purpose:
This formula will give us a result of 2 as you can see.