COUNTIFS
Counts rows that satisfy every one of several conditions at once.
COUNTIFS answers questions like how many trades settled late for a specific desk, how many clients in a region are above a certain tier, or how many support tickets are both open and high priority. It's a count, not a total, which is the first thing to get straight: if you need a sum, you want SUMIFS, not COUNTIFS.
COUNTIFS has no sum range at all, because it isn't adding anything up. It's the multi-condition version of COUNTIF the same way SUMIFS is the multi-condition version of SUMIF, and it shares the same condition-pair structure: a range to check, paired with what that range must match, repeated for as many conditions as you need.
Syntax
| Argument | Description |
|---|---|
| criteria_range1, criteria1 | Required. A range to test and the condition it must meet. |
| criteria_range2, criteria2, ... | Optional. Up to 126 more condition pairs, all required to be true. |
The mistake almost everyone makes
Treating the conditions as OR when COUNTIFS actually evaluates them as AND. A row only gets counted if every single condition pair matches at once, not if any one of them does. If you want an OR count (rows matching condition A or condition B), COUNTIFS on its own can't do it; you'd need two separate COUNTIFS calls added together, or a different approach entirely.
The second common mistake is expecting COUNTIFS to total something. It only counts rows; it never looks at a value to add up, because there's no sum_range argument in the syntax at all.
A real example: a settlement-exceptions report
Say you're reviewing a settlement-exceptions report and need to know how many trades for the Rates desk settled late, specifically in USD:
All three ranges, Desk_Range, Currency_Range, and Status_Range, need to cover the identical set of rows for the count to be accurate. This kind of multi-filter exception count is exactly what a daily ops or settlements report runs dozens of times over, one COUNTIFS per exception category.
Worked examples
| A | B | |
|---|---|---|
| 1 | Status | Value |
| 2 | Late | 250 |
| 3 | On Time | 300 |
| 4 | Late | 120 |
Common mistakes
- Expecting COUNTIFS to total something. It only counts; use SUMIFS if you need a total instead.
- Treating the conditions as OR instead of AND. A row must satisfy every one of them to be counted.
- Mismatched range sizes across the condition pairs, which silently drops rows from the count with no error at all.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice COUNTIFS →