Excel 2003 users used one of several workarounds: Helper columns, Array Formulas, SUMPRODUCT, etc.
Hopefully, Excel 2007 brings COUNTIFS, so you can forget those complicated methods outlined above.
You can create a COUNTIF two, three or N criteria very easily.
Here’s how the function works…
The syntax is:
COUNTIFS(range1, criteria1,range2, criteria2…) or
COUNTIFS(count the items in this range always that, they match criteria 1, and the items in range 2 match, criteria 2…)
You will now fully master COUNTIFS function by exploring a practical example.
We will use the table below.
Imagine you want to know how many YES answers per Age group (0 – 28, 28 – 35, 35 – 50,50 – 80).
Let’s build the formula…
- range N It is each column array with the values to be compared against criteria N. For example: $C$2:$C$11 or “Answer” against “YES”, $B$2:$B$11 or “Age” against the different Age groups criteria
- criteria N It is the value you want to search in each range N specified above. For example: “YES” and Age group between 0 and 28, etc
The formula in D2 would be (see table below): =COUNTIFS(Haystack!$C$2:$C$11,A2,Haystack!$B$2:$B$11,”>”&B2,Haystack!$B$2:$B$11,”<=”&C2)
The report would be this one:
Always cross check with a total sum (see D6 in the frontend). In this example the sum of partial quantities must be 4 since there are 4 “YES”. You can use a Filter to count with multiple criteria (sometimes items are not properly counted due to spaces or wrong spelling)