Count rows that match several conditions
Count how many rows meet two or more conditions at once — region AND status AND date — without adding a single helper column.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →COUNTIFS takes range/criteria arguments in pairs and ANDs every pair together — a row only counts if it satisfies all of them, which a single COUNTIF can't express.
- →Criteria can mix an exact match ("West") with a comparison operator (">100") in the same formula, because each pair is evaluated independently before the AND.
- →Adding a third condition is just another range/criteria pair on the end — the formula scales without restructuring what's already there.
The fine print
- →Every range argument must be the same size (same number of rows) as the others, or the formula returns a #VALUE! error.
- →Text criteria are case-insensitive and support wildcards (* and ?), which can quietly match more rows than expected on messy data.
- →Works identically in Excel and Google Sheets, wildcard behaviour included.
Part by part
- =COUNTIFS(
- Counts rows meeting every condition. The plural matters — COUNTIF takes one pair, COUNTIFS takes several and ANDs them.
- {{range1}}
- The first column to test. Every range in the formula must be the same size, since the conditions are evaluated row by row across them.
- {{criteria1}}
- The test applied to that range. Comparison operators go inside quotes as text, which is the syntax people most often get wrong.
- {{range2}}, {{criteria2}}
- Further pairs, always in that order. Every pair must be satisfied for a row to count.
Common mistakes
=COUNTIFS(A:A, >100, B:B, "East")An unquoted comparison is not valid syntax and the formula is rejected. Operators must be inside the quotes, as ">100", which reads oddly and is what the function expects.
=COUNTIFS(A:A, ">100", B:B, "East")=COUNTIFS(A2:A100, ">100", B2:B500, "East")Ranges of different sizes. Excel returns #VALUE! and Sheets may return a wrong count, because the row-by-row pairing has nothing to pair with beyond row 100.
=COUNTIFS(A:A, ">" & 100)Not wrong, and worth knowing: concatenating is how you build a criterion from a cell, as ">" & C1. Writing the literal inside quotes only works for a value that never changes.
Questions
How do I count rows matching either condition rather than both?
COUNTIFS only ANDs. For OR, add two COUNTIFS together and subtract a third counting rows matching both, otherwise those are counted twice. SUMPRODUCT is cleaner once there are more than two conditions.
How do I reference a cell in the criteria?
Concatenate: ">" & C1 builds the criterion from whatever C1 holds. Writing ">C1" compares against the literal text C1 and counts nothing, which is a quiet and common failure.
Does it treat text criteria case-sensitively?
No. "east" and "East" both match, which is usually convenient and occasionally wrong. Case-sensitive counting needs SUMPRODUCT with EXACT.
Can I use wildcards?
Yes — a question mark matches one character and an asterisk any number, so "East*" matches East and Eastern. To match a literal asterisk, precede it with a tilde.