How to Use the COUNTIFS Function in Excel
In this tutorial, you'll learn how to use the Excel COUNTIFS function.
COUNTIFS() is a function to count the number of cells in a range that meet multiple criteria.
You can use COUNTIFS() to count cells that contain dates, numbers, or references containing numbers.
COUNTIFS() Syntax
The syntax of the COUNTIFS() function is as follows:
=COUNTIFS(range1, criteria1, [range2, criteria2]...)
Arguments
- range1: The first range of cells that you want to count.
- criteria1: The first criteria that determine which cells will be counted
- range2: Additional range
- criteria2: Additional criteria
Remarks
- range1 and additional ranges (range2, range3, ...) can be a named range, arrays, or references containing numbers.
- criteria1 and additional criteria (criteria2...) can be a number, cell reference, expression, or text string.
- COUNTIFS() also supports the wildcard characters (?) and (*) in criteria for partial matching.
- COUNTIF() ignores the lower and upper case in text strings.
- You can specify up to 127 range/criteria pairs.
Excel COUNTIFS() Examples
Demo data:
| A | B | C | D | E | F | |
| 1 | Item Code | Item Name | Date | Qty | Unit Price ($) | Amount ($) |
| 2 | Item001 | Dell Laptop PC 1 | 16/4/2019 | 3 | 210 | 630 |
| 3 | Item001 | Dell Laptop PC 1 | 17/4/2019 | 5 | 210 | 1050 |
| 4 | Item002 | Dell Laptop PC 2 | 18/4/2019 | 1 | 250 | 250 |
| 5 | Item003 | Dell Laptop PC 3 | 18/4/2019 | 5 | 600 | 3000 |
| 6 | Item001 | Dell Laptop PC 1 | 19/4/2019 | 4 | 210 | 840 |
| 7 | Item004 | Dell Laptop PC 4 | 19/4/2019 | 2 | 210 | 220 |
| 8 | Item001 | Dell Laptop PC 1 | 20/4/2019 | 7 | 210 | 1470 |
Example 1
The following example counts the number of items that Unit Price is equal to 210:
=COUNTIFS(E2:E8, 210)
The above formula returns 5.
Example 2
The following example counts the number of items that Item Name is equal to HP Laptop PC 3.
=COUNTIFS(B2:B8, "HP Laptop PC 3")
The above formula returns 1.
Example 3
In the previous two examples, both formulas contain only a single criterion.
You can use COUNTIFS() with multiple criteria, as the following example shows:
=COUNTIFS(F2:F8, "<=1500", F2:F8, ">=840")
The above formula counts the number of items that the amount is between 840 and 1500.
The formula returns 3.
First criteria: the amount is less than or equal to 1500 in the range F2: F8.
Second criteria: the amount is greater than or equal to 840 in the same range.
Example 4
You can use the wildcard character (*) in the criteria to perform a partial matching, as the following example shows:
=COUNTIFS(B2:B8,"*PC 1",B2:B8, "Dell*")
There are two criteria. Both criteria contain the wildcard character (*).
The criteria *PC 1 matches any text before PC 1.
The criteria Dell* matches any text after Dell.
The above formula returns 4.
Example 5
You can use more than two ranges and two criteria, as the following example shows:
=COUNTIFS(B2:B8, "Dell *PC??", E2:E8, ">=250", E2:E8, "<500")
As you can see, there are three ranges and three criteria.
The criteria Dell *PC?? matches any text between Dell and PC, followed by any two characters after PC.
In this tutorial, you've learned how to use the Excel COUNTIFS function. COUNTIFS() is a function to count the number of cells that meet multiple criteria.
There are some other count functions in Excel, such as COUNT(), COUNTA(), COUNTBLANK(), and COUNTIF().
- To count only cells that contain numbers, use the COUNT function.
- To count only cells that are not blank, use the COUNTA function.
- To count the blank cells, use the COUNTBLANK function.
- To count cells that meet single criteria, use the COUNTIF function.