How to use the COUNTIFS function

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.

 

1. COUNTIFS Function Syntax

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

Back to top

2. COUNTIFS function 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.

Back to top

3. COUNTIFS function Example

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:

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.

Back to top

4. COUNTIFS function - Partial match using wildcard characters

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:

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

2.

Back to top

5. COUNTIFS function - 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

Back to top

6. COUNTIFS function - 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 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

Back to top

7. COUNTIFS function - 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

Back to top

8. COUNTIFS function - 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

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.

1. Select cells D9:D13.
2. Type the formula above: =COUNTIFS(B17,B9:B13,C17,C9:C13)
3. Press and hold CTRL + SHIFT simultaneously.
4. Press Enter once.
5. 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

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.

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.

Back to top

9. 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:

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.

1. Select cells E8:E9.
2. Type the formula above: =COUNTIFS(B3:B10,E3:E4,C3:C10,F3:F4)
3. Press and hold CTRL + SHIFT simultaneously.
4. Press Enter once.
5. 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

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 - 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}.

Back to top

9.3 Sum COUNTIFS conditions array

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:

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.

Back to top

10. COUNTIFS function - how to use 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:

1. Select cell range B3:B10.
2. Press and hold the CTRL key.
3. Press 1. Release the CTRL key. A dialog box appears.
   
4. Select Category: General.
   
5. Press with left mouse button on the OK button.
   
6. 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

Back to top

11. COUNTIFS function - past a given date

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:

Only one row meets both condition and that is row 6, the COUNTIFS function returns 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]…)

becomes

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

Step 2 - Evaluate the COUNTIFS function

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

becomes

COUNTIFS({45809; 45760; 45860; 46433; 45991; 45934; 46012; 45742},">1/1/2026",{"Lucy"; "Jennifer"; "Martin"; "Jennifer"; "Steve"; "Jennifer"; "Geoffrey"; "Steve"},"Jennifer")

and returns 1.

Back to top

12. COUNTIFS function - before a given date

This example demonstrates how to count records that equals "Jennifer" in column C specified in cell F3, and the corresponding date in column B is before a given date (1/1/2026) specified in cell E3.

Formula in cell E7:

I have highlighted cells that match the criteria, row 6 matches "Jennifer" but not the date condition. Both conditions must be met, however, rows 4 and 8 meet both conditions so the COUNTIFS function returns 2.

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]…)

becomes

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

Step 2 - Evaluate the COUNTIFS function

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

becomes

COUNTIFS({45809; 45760; 45860; 46433; 45991; 45934; 46012; 45742},"<1/1/2026",{"Lucy"; "Jennifer"; "Martin"; "Jennifer"; "Steve"; "Jennifer"; "Geoffrey"; "Steve"},"Jennifer")

and returns 2.

Back to top

13. How to make the COUNTIFS function work with a dynamic range

This example shows how to reference spilled values (a feature in Excel 365) in the COUNTIFS function. Spilled values happen when an Excel 365 function returns more than one value, it spills the remaining values below and sometimes to the right as well.

Cell range B15:D15 contains three different FILTER function formulas, they all use the specified value in cell B12 to extract records from cell range B3:D10.

Their results spill to cells below dynamically, by that I mean that if you change the condition in cell B12 their output change and the COUNTIFs function need to adjust to that change dynamically.

Excel 365 formula in cell B15:

Excel 365 formula in cell C15:

Excel 365 formula in cell D15:

COUNTIFS function in cell C27:

The formula above in cell C27 counts spilled rows in B15:D15 based on two conditions specified in cells C24 and D24.

The hashtag lets you reference values dynamically, try to change the condition in cell B12 to "A". The FILTER functions now return only one row, and the COUNTIFS automatically adjusts to the output size.

Back to top

14. How to count entries in Excel by date and an additional condition

This example demonstrates how to use the COUNTIFS function with two conditions, the first one is a date condition and the second is a text condition.

The first condition is specified in cell B14 (Date) and the second condition is in cell C14.

Formula in cell B17:

The formula in cell B17 returns 1 meaning the conditions are only found in one row which is row 6 in this example. The date condition is found in cell B6 and the text condition is found in C4, C6, and C8. However, cell C6 is the only one that counts. Remember, both conditions must match on the same row and the specified column.

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]…)

becomes

COUNTIFS(B3:B10,B14,C3:C10,C14)

Step 2 - Evaluate the COUNTIFS function

COUNTIFS(B3:B10,B14,C3:C10,C14)

becomes

COUNTIFS({45809; 45760; 45860; 45703; 45991; 45760; 46012; 45742},45703,{"Lucy"; "Elizabeth"; "Martin"; "Elizabeth"; "Steve"; "Elizabeth"; "Geoffrey"; "Steve"},"Elizabeth")

and returns 1.

Back to top

Back to top

'COUNTIFS' function examples

Functions in 'Statistical' category

The COUNTIFS function function is one of 73 functions in the 'Statistical' category.

Excel function categories

Excel categories

Comments (1)