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

: The first range of cells that you want to count.*range1*: The first criteria that determine which cells will be counted*criteria1*: Additional range*range2*Additional criteria*criteria2*:

### Remarks

and additional ranges (*range1*) can be a named range, arrays, or references containing numbers.*range2, range3, ...*and additional criteria (*criteria1*...) can be a number, cell reference, expression, or text string.*criteria2***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 a*mount* 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 a

*mount*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.