COUNTIF vs COUNTIFS in Google Sheets
Short answer: Use COUNTIF for one condition, COUNTIFS for two or more. Unlike SUM's cousins, the argument order is consistent here — COUNTIFS just adds more range/criterion pairs.
Copy the one most people want (COUNTIFS)
=COUNTIFS(A:A, "East", B:B, "Closed")
Counts rows where region (A) is East AND status (B) is Closed. Add more pairs for more conditions — all combined with AND.
COUNTIF vs COUNTIFS — the differences that matter
| Aspect | COUNTIF | COUNTIFS |
|---|---|---|
| Conditions | One only. | Two or more (AND). |
| Syntax | COUNTIF(range, criterion) | COUNTIFS(range1, crit1, range2, crit2, …) |
| Ranges must align | n/a — single range. | All ranges must be the same height or you get a mismatch error. |
| OR logic | Add two COUNTIFs. | AND only; for OR, add multiple COUNTIFS. |
Which one should you use?
Use COUNTIF when…
A single test — "how many cells equal Done", "how many are over 100". Shortest option for one rule.
Use COUNTIFS when…
Two or more tests that must all hold — region AND status, date range AND owner. Also the safe default if the number of conditions may grow.
Same task, both formulas
Count how many rows have region East in column A.
=COUNTIF(A:A, "East")
=COUNTIFS(A:A, "East")
Same count. COUNTIFS keeps the same argument style (range, criterion) so upgrading from one to many conditions is painless.
FAQ
Can COUNTIFS count with OR?
No — it's AND across all pairs. For OR, add separate COUNTIFS results, subtracting any overlap if needed.
Why do I get a range mismatch error?
Every range in COUNTIFS must span the same number of rows. Use matching ranges like A2:A100 and B2:B100.
Does COUNTIF support wildcards?
Yes — * matches any text and ? a single character, e.g. =COUNTIF(A:A, "East*").
→ Google Sheets formulas cheat sheet — 34 copy-paste formulas