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.


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

Demo data:

1Item CodeItem NameDateQtyUnit Price ($)Amount ($)
2Item001Dell Laptop PC 116/4/20193210630
3Item001Dell Laptop PC 117/4/201952101050
4Item002Dell Laptop PC 218/4/20191250250
5Item003Dell Laptop PC 318/4/201956003000
6Item001Dell Laptop PC 119/4/20194210840
7Item004Dell Laptop PC 419/4/20192210220
8Item001Dell Laptop PC 120/4/201972101470

 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.

See also:
Excel ROUNDUP Function with Examples
Excel MIN Function with Examples
Excel COUNTA Function – Count Non-Blank Cells in Excel
Excel COUNTBLANK Function – Count Blank Cells in Excel
Excel SUMIFS Function – Multiple Criteria

Leave a Comment