# Excel COUNTIFS Function with Examples

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

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

#### 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 functionCOUNTIFS() 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.