The COUNTIFS function in Excel
COUNTIFS counts rows where all conditions are true. Each condition is a pair: a range and what it must match. North orders above 100, paid orders between 100 and 300, and so on.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
| Argument | What it means |
|---|---|
| criteria_range1 | The first range to check. |
| criteria1 | The condition for that range, written like in COUNTIF: "North", ">100", a cell, or a wildcard. |
| criteria_range2, criteria2optional | More range/condition pairs. All ranges must be the same size. |
Examples
1. Two conditions in different columns
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | North over 100 | |
| 2 | 1001 | North | 250 | Paid | 2 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
Rows that are North and above 100: orders 1001 (250) and 1003 (420).
2. Between two values
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Amount 100–300 | |
| 2 | 1001 | North | 250 | Paid | 2 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
Use the same range twice, once for each limit: 250 and 130 are in the range.
3. Conditions taken from cells
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Region | North | |
| 2 | 1001 | North | 250 | Paid | Below | 200 | |
| 3 | 1002 | South | 90 | Paid | Count | 1 | |
| 4 | 1003 | North | 420 | Open | |||
| 5 | 1004 | East | 130 | Paid | |||
| 6 | 1005 | North | 75 | Paid |
North orders below 200: only order 1005 (75). Change G1 or G2 to ask a different question.
Common mistakes
#VALUE! error. The ranges have different sizes, e.g. B2:B6 and C2:C7.
Fix: Every criteria range must cover the same rows.
Expecting OR logic. COUNTIFS only counts rows where every condition is true.
Fix: For North or East, add two counts:
=COUNTIF(B2:B6,"North")+COUNTIF(B2:B6,"East").
Frequently asked questions
How many conditions can COUNTIFS take?
Up to 127 range/condition pairs.
Can I use COUNTIFS with only one condition?
Yes, it then works exactly like COUNTIF.