Function guides · Statistical
COUNTIFS function
Counts the rows that meet every one of several conditions.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
| Argument | What it is |
|---|---|
criteria_range1 | The first range to test. |
criteria1 | The condition for that range. |
... | Up to 127 range and condition pairs. A row counts only if every condition is met. |
What COUNTIFS does
COUNTIFS counts the rows where every condition you list is true. It is COUNTIF with as many conditions as you need.
Each condition is a pair: the range to look in, then the test to apply. Excel goes row by row, and a row is counted only if it passes all the tests. That makes it an AND filter by design. Because the ranges are compared position by position, they must be the same shape.
Date windows, value bands and “this region and that product” counts are the typical uses. For the matching sum rather than count, see SUMIFS, which takes its arguments in a slightly different order.
COUNTIFS examples
1. Two conditions on two columns
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sale | ||
| 2 | West | 450 | 2 | |
| 3 | East | 900 | ||
| 4 | West | 620 | ||
| 5 | North | 310 | ||
| 6 | West | 275 |
A row counts only when the region is West and the sale is over 300. West has three rows, and two of them clear 300.
Each condition comes as a pair: the range, then the test. The ranges can be different columns, as here, but they must all be the same size.
2. A number between two values
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sale | ||
| 2 | West | 450 | 3 | |
| 3 | East | 900 | ||
| 4 | West | 620 | ||
| 5 | North | 310 | ||
| 6 | West | 275 |
To count values between 300 and 700, use the same range twice, once with a lower bound and once with an upper bound. 450, 620 and 310 fall inside.
Use >= and <= when the endpoints should be included, as they are here.
3. Orders inside a date window
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Date | ||
| 2 | 1001 | 2/27/2026 | 2 | |
| 3 | 1002 | 3/4/2026 | ||
| 4 | 1003 | 3/19/2026 | ||
| 5 | 1004 | 4/2/2026 |
Dates are numbers, so they take the same operators. This counts orders placed in March 2026: on or after March 1 and before April 1. Building the dates with DATE avoids any locale trouble with typed strings.
Common COUNTIFS mistakes
- Ranges of different sizes.
=COUNTIFS(A2:A6,"West",B2:B7,">300")returns #VALUE!. Every range must have the same number of rows and columns. - Expecting OR logic. COUNTIFS is AND only. To count West or East, add two COUNTIFS together, or use SUM(COUNTIFS(range,{"West","East"})).
- Forgetting the ampersand with cell criteria.
">"&E1is correct.">E1"compares with the text E1.
COUNTIFS on Excel job tests
Break the sentence into pairs before you type. "How many orders from the West region over $300 were placed in March" is four pairs: region equals West, amount above 300, date on or after March 1, date before April 1. That is three columns, with the date column used twice.
If the sheet has a summary table, take the region and month from its headers using cell references, and lock the data ranges with dollar signs so the formula can be filled across and down. A quick check is to filter the data to the same conditions and compare the row count in the status bar.
Decide on every boundary: >= or >, and whether the end date is included.
Related functions
COUNTIFS questions
- How many conditions can COUNTIFS have?
- Up to 127 range and condition pairs.
- Can COUNTIFS use OR?
- Not directly. Add separate COUNTIFS together, or pass an array of criteria inside SUM.
- Why does COUNTIFS return
- The ranges are different sizes. Check that each one covers the same number of rows.
- What is the difference between COUNTIF and COUNTIFS?
- COUNTIF takes one range and one condition. COUNTIFS takes up to 127 pairs and counts only rows that meet all of them.
Practice COUNTIFS
Try it in a working grid: the free lesson COUNTIF: count what matches (module 8 of Everyday Excel) has checked COUNTIFS exercises.
Practice this function