How to use the COUNTIFS function in Excel

The COUNTIFS function helps count cells based on multiple conditions at the same time. This is an important function in Excel, supporting fast and accurate data processing.

Table of Contents:
1. What is the COUNTIFS function?
2. Usage syntax
3. Some notes when using
4. Specific examples
5. How to use the COUNTIFS function with wildcard characters
6. Example of the COUTIFS function with dates

1. What is the COUNTIFS function?

The COUNTIFS function is an advanced function of the COUNTIF function used to count the number of data cells that match multiple accompanying conditions.

Application:

+ Count the number of data cells according to multiple conditions.
+ Work more professionally when using Excel.
+ Easily combine with other functions to support work.

2. Usage syntax

COUNTIFS supports logical operators (>, <,><>, =) and wildcards (*,?) to combine parts.

Syntax: COUNTIFS(Criteria_range1,Criteria1,[Criteria_range2,Criteria2],…)

In there:

- range1 – first range to evaluate.
- criteria1 - defines the first range to which the first criterion (criteria1) applies, required.
- range2 - [additional criteria] the second range to evaluate.
- criteria2 - [additional criteria] defines the range to which the second criterion applies.

3. Some notes when using

COUNTIFS counts the number of cells in a range that match the provided criteria. Unlike the COUNTIF function, the COUNTIFS function can apply more than one criterion to more than one range. The ranges and criteria are applied in pairs, and only the first pair is required. For each additional criterion, you must supply additional range/criteria pairs. Up to 127 range/criteria pairs are allowed.

Note: 

1. - Each additional data range must have the same number of rows and columns as the first data range (range 1). COUNTIFS can count contiguous and non-contiguous data ranges.
2. - If the provided data range does not match, you will receive a #VALUE error.
3. - Non-numeric conditions do not need to be enclosed in quotation marks, but numeric conditions do. For example: 100, '100', '> 32', 'jim', or A1 (where A1 contains a number).
4. - The wildcard characters ? and * can be used in the condition. The question mark (?) can match any character, and the asterisk (*) matches any sequence of characters.
5. - To find a question mark (?) or an asterisk (*), use a tilde (~) before the preceding question mark or asterisk (i.e. ~ ?, ~ *).
6. - The structure of the COUNTIFS function is quite different from the Count function . The COUNTIFS function can have 127 pairs of Criteria_range,Criteria.
7. - Criteria_range is one or more numeric, named, array, or reference cells containing numbers.
8. - Criteria is in the form of a number, expression, cell reference, or specified text string. Blank cells or cells containing text data are ignored.

Tip: To count cells containing data in a spreadsheet, use the Counta function.

4. Illustrative example

Example 1 : Given a score sheet of some students, count the number of students who got 9 and 8 in all 3 subjects:

Count the number of students who got all 3 9s with the formula

The formula in cell D11 is: = COUNTIFS(D5:D9,9,E5:E9,9,F5:F9,9)

If you want to count the number of students who got all 3 8s, use the following formula:

The formula in cell D11 is: = COUNTIFS(D5:D9,8,E5:E9,8,F5:F9,8)

And you have counted the number of students who got all 3 8s or 3 9s at the same time, similarly with other scores you do the same. You can also filter different scores.

5. How to use the COUNTIFS function with wildcard characters

+ Question mark (?) – matches any single character, use it to count cells that start or end with a certain number of characters.
+ Asterisk (*) – matches any sequence of characters, use it to count cells that contain a specific word or k1 characters in a cell.

Note. If you want to count cells containing a question mark or asterisk, type a tilde (~) before the asterisk or question mark.

For example, let's say you have a list of projects in column A. You want to know how many projects have been signed, i.e. column B contains people's names. Let's add a second condition – End date in column D.

=COUNTIFS(B2:B10,'*',D2:D10,'<>'&''))

6. Example of COUTIFS function with date

Example 1: Counting days in a specific period

In the example below, count the number of days from 05/02/2022 => 02/28/2022 , you apply the following formula:

=COUNTIF(D2:D15,">=05/02/2022")-COUNTIF(D2:D15,">05/02/2022")

Results received in 5 days

Example 2: Count days with multiple conditions

In the example below you will find the number of days with products imported after 02/05/2022 and sold after 02/28/2022.

Formula: =COUNTIFS(D2:D15,">02/05/2022",E2:E15,">02/28/2022")

=> Result: 7 days

Example 3: Count days with multiple conditions based on current date

With the formula =COUNTIFS(D2:D15,"<"&today(),e2:e15,">"&TODAY()) you will be able to select products that have been purchased but have not been sold to date.

=> Result: 1 day

The COUNTIFS function in Excel helps to count data based on multiple conditions accurately. You can apply this function to count the number of orders by day, the number of students with high scores by subject or other criteria in the spreadsheet.

In addition, if you need to count the number of blank cells in Excel, use the  COUNTBLANK function . This function supports checking the number of cells without data, making it easy to control missing information in the report. To improve your statistical skills, you can refer to  more advanced statistical functions  in Excel to help optimize data processing.

Kareem Winters

Update 20 February 2025