COUNTIF vs COUNTIFS: one letter, one big difference
COUNTIFS is COUNTIF's multi-condition sibling — and unlike the SUMIF/SUMIFS pair, the argument order doesn't flip, so the safest habit is simply: always use COUNTIFS.
The differences at a glance
| COUNTIF | COUNTIFS | |
|---|---|---|
| Number of conditions | Exactly one | One or many (all ANDed) |
| Argument pattern | range, criteria | range1, criteria1, range2, criteria2, ... |
| Wildcards and comparisons | Yes ("Widg*", ">100") | Yes, per condition |
| OR counting | Add COUNTIFs (beware overlap) | Same — COUNTIFS is AND-only; use SUMPRODUCT for true OR |
| Compatibility | Universal | Universal (Excel 2007+, every Sheets & LibreOffice version we test) |
Which should you use?
- COUNTIF — Legacy sheets that already use it — there's no functional advantage over a one-pair COUNTIFS.
- COUNTIFS — Everything new. Same syntax for one condition or five, no relearning when requirements grow.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
Both execute correctly in every LibreOffice version we test; Google Sheets matches in our executed run; desktop Excel behavior matches per Microsoft's documentation — we do not run desktop Excel. One shared quirk to know: criteria ranges must be the same size, and text criteria over ~255 characters fail in Excel — neither limit differs between the two functions. Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 9 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
Example formulas
| COUNTIF | =COUNTIF(A2:A100,"East") |
| COUNTIFS (two conditions) | =COUNTIFS(A2:A100,"East",B2:B100,">100") |
Full per-version details on each function page: COUNTIF · COUNTIFS.