The COUNTIFS function counts the number of cells across multiple ranges that meet all given conditions. It allows you to use up to 254 arguments or 127 criteria pairs.

To make the function more useful Microsoft made it possible to use wildcards * and question marks ?  in the criteria arguments. ? matches a single character while * matches any sequence of characters.

The really interesting stuff happens when you use the function in an array formula. The number of values in each criteria argument determines how many values the function returns. If one value is used, like in example 1 and 2. A single value is returned.

In example 3 and 4, five values are used in each criteria argument and the countifs function returns five values. Remember to enter the formulas as an array formula.

Example 1

The following formula counts how many times AA and 10 together exists in cell range B9:B13 and C9:C13.

COUNTIFS function1_1

COUNTIFS(B9:B13,E9,C9:C13,F9)

becomes

COUNTIFS({"AA";"BB";"CC";"AA";"BB"},"AA",{10;20;30;10;40},10)

and returns 2 in cell G9.

Here is a post where I use this technique: Highlight duplicate rows

Example 2

The formula below counts how many times text string "Han" and dates in february 2013 exists in cell range B9:B13 and C9:C13.

COUNTIFS function2

=COUNTIFS(B9:B13,">="&E9,B9:B13,"<="&F9,C9:C13,G9)

becomes

=COUNTIFS({41275;41307;41324;41336;41325},">="&41306,{41275;41307;41324;41336;41325},"<="&41333,{"Luke";"Han";"Ben";"Luke";"Han"},"Han")

2 is returned in cell E12.

Here is a post where I use comparison operators: Filter overlapping date ranges

Example 3

The following array formula counts each cell value in each row and returns an array.

COUNTIFS function3

=COUNTIFS(B9:B13,B9:B13,C9:C13,C9:C13)

becomes

=COUNTIFS({"Asia"; "Africa"; "Asia"; "Asia"; "Asia"},{"Asia"; "Africa"; "Asia"; "Asia"; "Asia"},{"Luke"; "Han"; "Ben"; "Luke"; "Han"},{"Luke"; "Han"; "Ben"; "Luke"; "Han"})

and returns {2;1;1;2;1} in cell range D9:D13.

Here are two posts where I use this technique:

Example 4

In this example I am using a cell value as a criteria_range and a cell range as criteria. This may seem confusing but it is definitely possible and very useful.

COUNTIFS function4

=COUNTIFS(B17,B9:B13,C17,C9:C13)

becomes

=COUNTIFS("Asia", {"Asia"; "Africa"; "Asia"; "Africa"; "Australia"}, "Ben", {"Luke"; "Han"; "Ben"; "Luke"; "Han"})

and returns {0; 0; 1; 0; 0} in cell range D9:D13.

Download excel *.xlsx file

COUNTIFS function.xlsx