How to use the COUNTIFS function

Author: Oscar Cronquist Article last updated on January 13, 2025

What is the COUNTIFS function?

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.

What's on this page

Syntax
Arguments
Example 1
Example 2 - Partial match using wildcard characters
Example 3 - using two conditions
Example 4 - Logical operators
Example 5 - Count duplicate records
Example 6 - return an array identifying records based on multiple conditions
Example 7 - OR logic
Example 8 - how to use dates
Example 9 - after a date
Example 10 - before a date
How to make the function work with a dynamic range
How to count entries in Excel by date and an additional condition
Function not working
Get Excel *.xlsx file

1. Syntax

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

2. Arguments

criteria_range1	Required. The cell range you want to count the cells meeting a condition.
criteria1	Required. The condition that you want to count.
[criteria_range2]	Optional. Additional ranges, up to 127 pairs.
[criteria2]	Optional. Additional ranges, up to 127 pairs.

3. Example 1

The COUNTIFS function counts the number of rows that matches one or more conditions. The image above shows the following data table in cell range B3:C10:

Name	Score
Lucy	5
Elizabeth	3
Martin	2
Andrew	4
Lucy	5
Jennifer	3
Geoffrey	6
Abraham	3

Formula in cell E3:

=COUNTIFS(B3:B10,"Lucy",C3:C10,5)

Lucy and 5 are found twice, in rows 3 and 7. The COUNTIFS function returns 2 in cell E3 which represents the number of times both conditions match on a single row. All conditions must be met on the same row.

The COUNTIFS function lets you use many condition pairs, this example shows only two. A pair meaning a condition and the corresponding cell range you want to use.

This function is excellent for counting unique, unique distinct or duplicate rows etc. You can do some seriously complicated calculations with this function.

4. Example 2 - Partial match using wildcard characters

COUNTIFS function partial match wildcard

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 image above shows this data table in cell range B3:C10:

Name	Country
Lucy	Canada
Elizabeth	US
Martin	France
Andrew	Spain
Steve	Italy
Jennifer	Canada
Geoffrey	Italy
Abraham	France

Cell E3 contains the following condition: *n*, cell F3 contains the second condition which is *an* The asterisks allow you to match any characters from zero to any number.

For example, *n* matches cell B5 because the value ends with a "n", cell C5 matches *an* because it is found in this value "France", in other words, the asterisk allows you to perform a partial match.

Formula in cell E6:

=COUNTIFS(B3:B10, E3, C3:C10, F3)

The next match is found in cell B8, "Jennifer" contains the character "n" and cell C8 which is "Canada" contains "an". Remember that both conditions must be matched for the COUNTIFS function to add the row to the total count.

There are plenty more matches in B3:C10, however, rows 5 and 8 are the only ones that matches both conditions.

4.1 Explaining formula

The Evaluate Formula tool is located on the Formulas tab in the Ribbon. It is a useful feature that allows you to step through and evaluate complex formulas to understand how the calculation is being performed and identify any errors or issues. The following steps shows these detailed evaluations for the formula above.

Step 1 - Populate arguments

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

criteria_range1 - B3:B10
criteria1 - E3
criteria_range2 - C3:C10
criteria2 - F3

COUNTIFS(B3:B10, E3, C3:C10, F3)

Step 2 - Evaluate COUNTIFS function

COUNTIFS(B3:B10, E3, C3:C10, F3)

becomes

COUNTIFS({"Lucy"; "Elizabeth"; "Martin"; "Andrew"; "Steve"; "Jennifer"; "Geoffrey"; "Abraham"}, "*n*", {"Canada"; "US"; "France"; "Spain"; "Italy"; "Canada"; "Italy"; "France"}, "*an*")

The asterisk matches 0 (zero) to any number of characters, and a leading and trailing asterisk "*n*" matches any value that contains the character n in cells B3:B10. "Martin", "Andrew", "Jennifer" contains a "n"

"*an*" matches any country in cells C3:C10 that contains "an", they are found in rows 3, 5, 8, and 10. The COUNTIFS function counts a row if both conditions are met.

COUNTIFS({"Lucy"; "Elizabeth"; "Martin"; "Andrew"; "Steve"; "Jennifer"; "Geoffrey"; "Abraham"}, "*n*", {"Canada"; "US"; "France"; "Spain"; "Italy"; "Canada"; "Italy"; "France"}, "*an*")

returns

5. Example 3 - 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.

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

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

6. Example 4 - 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.

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

Three conditions in total are applied to two cell ranges, this also demonstrates that you can use logical operators with conditions. Make sure you use double quotes enclosing 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 are in the month of February 2013.

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

7. Example 5 - Count duplicate records

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

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

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:

8. Example 6 - return an array identifying records based on multiple conditions

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.

The image above shows this data table in cell range B8:C13:

criteria_range1	criteria_range2
Asia	Luke
Africa	Han
Asia	Ben
Asia	Luke
Asia	Han

The first condition is specified in cell B17 and the second condition in cell C17. We are using one condition in the criteria1 argument and a cell reference to one cell in the criteria_range argument. This is true for the second condition as well.

Array formula in cell D9:D13

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

This setup makes the COUNTIFS function return an array as large as the source data range. The output returns 1 if the row matches the criteria and 0 (zero) if not.

For example, the output from the formula is displayed in cell D9 and cells below. They correspond to the source data range specified in B8:C13 meaning that 1 identifies that particular row as a match. The example above returns 1 only for row 11, in other words, row 11 is the only record that matches both conditions.

8.1 How to enter an array formula

Excel 365 users can skip these steps, press Enter to enter the formula like a regular formula.

Select cells D9:D13.
Type the formula above: =COUNTIFS(B17,B9:B13,C17,C9:C13)
Press and hold CTRL + SHIFT simultaneously.
Press Enter once.
Release all keys.

The formula is now enclosed with curly brackets like this: {=COUNTIFS(B17,B9:B13,C17,C9:C13)}

Don't enter these characters yourself, they appear automatically if you followed the steps above.

8.2 Explaining the formula

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.

Row three meets all conditions, 1 is in the third position of the array.

9. Example 7 - OR logic

COUNTIFS function or logic

This example shows that using this specific setup the COUNTIFS function performs OR logic between criteria records specified in cells E3:F3 and E4:F4

The way this works is that the array formula evaluates both records against B3:B10 and C3:C10 and returns an array containing numbers that correspond to the position of the records.

The image above has the following data table in cell range B2:C10:

Name	Country
Lucy	Canada
Elizabeth	US
Martin	France
Andrew	Spain
Steve	Italy
Jennifer	Canada
Geoffrey	Italy
Steve	Italy

Array formula in cells E8:E9:

=COUNTIFS(B3:B10,E3:E4,C3:C10,F3:F4)

For example, the image above demonstrates two conditions. The result is an array with two values. The first value is 1 meaning the first conditions are found once in B3:C10.

The second value is 2 meaning the second conditions are found twice in B3:C10. The image shows has the matching rows in a different background color.

The next example in section 10 shows how to add the number sin the array.

9.1 How to enter the array formula in cells E8:E9

Excel 365 users can skip these steps, press Enter to enter the formula like a regular formula.

Select cells E8:E9.
Type the formula above: =COUNTIFS(B3:B10,E3:E4,C3:C10,F3:F4)
Press and hold CTRL + SHIFT simultaneously.
Press Enter once.
Release all keys.

The formula is now enclosed with curly brackets like this: {=COUNTIFS(B3:B10,E3:E4,C3:C10,F3:F4)}

Don't enter these characters yourself, they appear automatically if you followed the steps above.

9.2 Explaining formula

Step 1 - Populating arguments

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

becomes

COUNTIFS(B3:B10,E3:E4,C3:C10,F3:F4)

Step 2 - Evaluate the COUNTIFS function

COUNTIFS(B3:B10,E3:E4,C3:C10,F3:F4)

becomes

COUNTIFS({"Lucy"; "Elizabeth"; "Martin"; "Andrew"; "Steve"; "Jennifer"; "Geoffrey"; "Steve"},{"Elizabeth"; "Steve"},{"Canada"; "US"; "France"; "Spain"; "Italy"; "Canada"; "Italy"; "Italy"},{"US"; "Italy"})

and returns

{1; 2}.

9.3 Sum COUNTIFS conditions array

COUNTIFS function or logic1

This formula adds the numbers for each record and returns a total. 3 represents the number of times the conditions in E3:F3 OR E4:F4 are found in the data table in cell range B3:C10.

Array formula in cell E8:

=SUM(COUNTIFS(B3:B10,E3:E4,C3:C10,F3:F4))

The SUM function adds the numbers in the array and returns a total.

So if we continue from step 2 above.

SUM(COUNTIFS(B3:B10,E3:E4,C3:C10,F3:F4))

becomes

SUM({1; 2})

and returns 3 in cell E8.

10. Example 8 - how to use dates

COUNTIFS function dates

First, we need to understand how Excel works with dates. Excel dates are whole numbers formatted as dates. It begins with 1/1/1900 as 1, and 1/1/2000 is 36526.

Here is how to show the numbers behind the dates:

Select cell range B3:B10.
Press and hold the CTRL key.
Press 1. Release the CTRL key. A dialog box appears.
Select Category: General.
Press with left mouse button on the OK button.
To go back to dates press and hold the CTRL key, then press z. Release the CTRL key. This keybard shortcut lets you undo the last step.

Another way to undo the last step is to go to tab "Home" on the ribbon. Press with left mouse button on the "Undo" button, see the image above.

We now know that Excel dates are numbers and the COUNTIFS function can easily process numbers.

Use the logical operators in the COUNTIFS function for added functionality.

< less than sign
> larger than sign
= equal sign

11. Example 9 - past a given date

COUNTIFS function after a date1

This example counts rows that meet two conditions, the first condition is specified in cell F3 (Jennifer). It is matched to the values in column C.

The second condition is specified in cell E3, it is a date condition combined with a logical operator (>1/1/2026). This means that it matches dates later than 1/1/2026.

Formula in cell E7:

=COUNTIFS(B3:B10,E3,C3:C10,F3)

Only one row meets both condition and that is row 6, the COUNTIFS function returns 1.