The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.

It allows you to use up to 254 arguments or 127 criteria pairs.

Formula in cell E3:

Lucy and 5 are found twice, in row 3 and 7, the function returns 2 in cell E3.

Excel Function Syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Arguments

Comments

The optional wildcard characters make the COUNTIFS function even more powerful, these characters are * asterisk and question marks ? The question mark ? matches a single character while the * asterisk matches any sequence of characters even 0 (zero) characters.

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

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

Example 1 - Using two conditions

This example demonstrates the COUNTIFS function with two conditions applied to two separate columns. The COUNTIFS function counts a record when both conditions are met on the same row.

This formula checks if text string "AA" is found in cell range B9:B13 and if 10 is found in cell range C9:c13. Row 9 and 12 contain both conditions and 2 are returned to cell G9.

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.

The calculation above shows two arrays and ou can tell that by the curly brackets {}.

The values in those arrays are separated by a semicolon meaning the values are on a row each.

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

Example 2 - Logical operators

The following formula counts how many times text string "Han" equals a cell value in cell range C9:C13, it also checks if dates in cell range B9:B13 are larger than or equal to February 1st, 2013 and smaller than or equal to February 28, 2013.

Three conditions in total applied to two cell ranges, this also demonstrates that you can use logical operators with conditions. Make sure you use double-quotes around the logical operators and an ampersand to concatenate the values with the logical operators.

Here is a list of all logical operators you can use and their combinations.

• = equal to
• < less than
• > larger than
• <= less than or equal to
• >=larger than or equal to
• <> not equal to

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")

and returns 2 in cell E12. Row 9 and 13 have the word "Han" and is in month February 2013.

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

Example 3 - Count duplicate records

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

I demonstrated in example 1 and 2 above how to use a single condition in each criteria argument, the formula above demonstrates what happens if you use multiple conditions.

Note, you will receive an error if you don't use the same number of conditions in each criteria argument.

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 this array {2; 1; 1; 2; 1} in cell range D9:D13. This array is interesting because it identifies how many times each record occurs in the data set.

Here are two posts where I use this technique:

• Count unique distinct records
• Filter unique distinct row records

Example 4 -

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

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.

Get the Excel file

COUNTIFS-function.xlsx