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.
The syntax of the COUNTIFS() function is as follows:
=COUNTIFS(range1, criteria1, [range2, criteria2]...)
- 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
- 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
|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|
The following example counts the number of items that Unit Price is equal to 210:
The above formula returns 5.
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.
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.
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.
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.