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

### What's on this page

- COUNTIFS function Syntax
- COUNTIFS function Arguments
- COUNTIFS function example
- COUNTIFS function - Partial match using wildcard characters
- COUNTIFS function - using two conditions
- COUNTIFS function -Â Logical operators
- COUNTIFS function - Count duplicate records
- COUNTIFS function - return an array identifying records based on multiple conditions
- COUNTIFS function - OR logic
- COUNTIFS function - how to use dates
- COUNTIFS function - after a date
- COUNTIFS function - before a date
- How to make the COUNTIFS function work with a dynamic range
- How to count entries in Excel by date and an additional condition
- Get Excel *.xlsx file

## 1. COUNTIFS Function Syntax

COUNTIFS(*criteria_range1*, *criteria1*, [*criteria_range2*, *criteria2*]â€¦)

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

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

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

## 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

## 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

## 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:

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

- 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

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.

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

- 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

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

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

## 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:

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

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

## 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

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

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

## 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

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

### 'COUNTIFS' function examples

This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may […]

Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]

Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]

### Functions in 'Statistical' category

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

### Excel function categories

### Excel categories

### One Response to “How to use the COUNTIFS function”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

[…] COUNTIFS(criteria_range1,criteria1, criteria_range2,Â criteria2...) Counts the number of cells specified by a given set of conditions or criteria […]