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

 ABCDEF
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