Write a COUNTIF Multiple very Easily with the New COUNTIFS

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.

Countif multiple table

Countif multiple table

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:

Important notice:

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)