COUNTIFS
Count rows that satisfy every one of a set of criteria.
Syntax
COUNTIFS(range1, criteria1, [range2, criteria2], ...)| Argument | Required | Description |
|---|---|---|
range1 | Required | The first range to test. |
criteria1 | Required | The condition applied to it. |
range2 | Optional | Further range/criteria pairs. All must hold for a row to count. |
Examples
Every example below was executed against VisiGrid engine 0.35.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=COUNTIFS(B1:B5,"East",A1:A5,">15") | 2 | East rows with a value above 15. |
=COUNTIF(B1:B5,"East") | 3 | Without the second condition, all three East rows count. |
Use it even for one condition
COUNTIF and COUNTIFS take their arguments in the same
order, unlike SUMIF and SUMIFS,
so there is no trap here — but there is still a case for defaulting to the plural
form. Adding a second condition later means appending two arguments rather than
rewriting the formula.
Every range must be the same shape
Each criteria range has to match the others in height. A mismatch is the classic way a count drifts after someone inserts a row: one range grows, another does not, and the answer quietly changes.
Excel compatibility
Matches Excel.