COUNTIFS - Count rows meeting every condition
Build a dispatch count that combines service, readiness, and deadline rules on the same order.
By the end, you can
- Pair each test range with its condition.
- Count records satisfying all rules.
- Use inclusive time or numeric boundaries.
Before you start: Understand COUNTIF conditions and aligned ranges.
In this lesson
Count eligible records, not separate matches
Dispatch needs Express orders that are Ready and still have at least two hours before their deadlines. COUNTIFS counts an order only if all three conditions pass on that order. Separate counts of Express, Ready, and timely orders cannot tell you how many satisfy their intersection.
Syntax and argument pairs
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)Supply at least one range-and-criterion pair. Up to 127 pairs are supported in Excel.
| Argument | Purpose | Example |
|---|---|---|
criteria_range1, criteria1Required | The first test column and its condition. | B2:B6, "Express" |
additional pairsOptional | Other conditions applied to aligned cells on the same records. | D2:D6, ">=2" |
The next dispatch list
2| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Service | Status | Hours left | Dispatch count |
| 2 | O201 | Express | Ready | 4 | 2 |
| 3 | O202 | Standard | Ready | 3 | |
| 4 | O203 | Express | Pending | 6 | |
| 5 | O204 | Express | Ready | 2 | |
| 6 | O205 | Express | Ready | 1 |
Remove or change a rule deliberately
=COUNTIFS(B2:B6,"Express",C2:C6,"Ready")O201, O204, and O205 pass these two conditions.
=COUNTIFS(D2:D6,">=2",D2:D6,"<=4")The 4-, 3-, and 2-hour records fall in this inclusive band, independent of service and status.
=COUNTIFS(B2:B6,"Express",C2:C6,"Ready",D2:D6,"<2")Only O205 matches. This is a separate exception report.
Alignment and AND logic
- All conditions must pass on the corresponding cells of one record. They are combined as AND, not OR.
- Every criteria range must have the same dimensions. Misaligned row starts can pair unrelated records even when range lengths match.
- No matching records returns 0. Duplicate records still count separately.
- Text matching ignores case, and text criteria support * and ? wildcards. Quoted operators can be joined to criteria cells with &.
Common mistakes and fixes
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.