COUNTIF counts cells that match one condition. COUNTIFS counts rows that match several conditions at once. They share the same operators and wildcards — the only real difference is how many conditions you can apply. Below: 12 capabilities scored honestly, 7 behavioral differences measured live in the same engine, and a guide to picking the right one.
They are not rivals — COUNTIFS is the multi-condition extension of COUNTIF. For a single test, COUNTIF's two-argument form is simpler; the moment you need a second condition, COUNTIFS is the only clean option. Operators and wildcards behave identically in both.
Every row is a real capability difference. Operators, wildcards and case-insensitivity are identical; condition count and argument structure are where they diverge.
| Capability | COUNTIF | COUNTIFS | Why it matters |
|---|---|---|---|
| Conditions supported | ✓ Exactly 1 | ✓ 1 to 127 | The defining difference. |
| Argument order | ✓ range, criteria | ✓ range1, criteria1, … | COUNTIFS pairs each range with its criteria. |
| Comparison operators | ✓ >, <, >=, <>, = | ✓ >, <, >=, <>, = | Identical; quoted, e.g. ">5". |
| Wildcards (* ?) | ✓ Yes | ✓ Yes | Both match text patterns; ~ escapes. |
| Case sensitivity | ✓ Case-insensitive | ✓ Case-insensitive | "east" matches "East" in both. |
| Multiple criteria ranges | ✗ One range | ✓ One per condition | COUNTIFS can test different columns. |
| AND logic | ✗ Not applicable | ✓ Native | All COUNTIFS conditions must hold. |
| OR logic | ◐ SUM + array constant | ◐ Add separate calls | Neither has native OR. |
| Count blank cells | ✓ criteria "" | ✓ criteria "" | COUNTBLANK is the dedicated tool. |
| Count non-blank | ✓ criteria "<>" | ✓ criteria "<>" | COUNTA is simpler for one range. |
| Whole-column ranges | ✓ A:A | ✓ A:A | Both accept them; bounded ranges are faster. |
| Excel version | ✓ All versions | ✓ 2007+ | COUNTIFS arrived in Excel 2007. |
COUNTIF takes one range and one criteria. COUNTIFS takes pairs — each criteria_range is followed by its criteria — and can hold up to 127 pairs.
Type your own COUNTIF and COUNTIFS formulas against the live grid and watch the count update.
These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.
=COUNTIF(A2:A8,"East")=COUNTIFS(A2:A8,"East")=COUNTIF(A2:A8,"East")=COUNTIFS(A2:A8,"East",B2:B8,"Widget")=COUNTIF(C2:C8,">5")=COUNTIFS(C2:C8,">5")=COUNTIF(B2:B8,"W*")=COUNTIFS(B2:B8,"W*")=SUM(COUNTIF(A2:A8,{"East","West"}))=COUNTIFS(A2:A8,"East")+COUNTIFS(A2:A8,"West")=COUNTIF(B2:B6,"")=COUNTIFS(B2:B6,"")=COUNTIF(A2:A8,"<>East")=COUNTIFS(A2:A8,"<>East")Same dataset, same engine, same machine — relative cost only. Both are fast; COUNTIFS does slightly more work per row as conditions are added.
| Table size | COUNTIF | COUNTIFS |
|---|---|---|
| 1,000 rows × 500 lookups | 331.2 ms | 257.41 ms |
| 5,000 rows × 500 lookups | 1985.5 ms | 1995.27 ms |
| 10,000 rows × 500 lookups | 5919.7 ms | 5845.74 ms |
Match your situation to a recommendation. The rule of thumb: one condition → COUNTIF; two or more → COUNTIFS.
| Situation | Use | Why |
|---|---|---|
| Counting cells that match a single condition | COUNTIF | Two arguments, nothing extra to manage. |
| Counting rows that must match two or more conditions | COUNTIFS | AND across conditions is built in. |
| Each condition lives in a different column | COUNTIFS | One criteria_range per condition. |
| You want a numeric threshold like >5 or <=100 | COUNTIF | One condition — operators work the same in both. |
| You need OR (this or that) | COUNTIF | SUM(COUNTIF(range,{"a","b"})) is the compact idiom. |
| Counting blank cells in one range | COUNTIF | criteria "" works, though COUNTBLANK is clearer. |
| Building a summary table broken down by several fields | COUNTIFS | Region + product + date in one formula. |
| Supporting workbooks older than Excel 2007 | COUNTIF | COUNTIFS does not exist before 2007. |
Common COUNTIF tasks rewritten in COUNTIFS. Single-condition formulas are unchanged; the gain appears when you add conditions.
| Task | COUNTIF / old way | COUNTIFS | What changed |
|---|---|---|---|
| Count one region | =COUNTIF(A2:A8,"East") | =COUNTIFS(A2:A8,"East") | Identical result; COUNTIFS just adds room for more conditions. |
| Region AND product | =COUNTIF(A2:A8,"East") | =COUNTIFS(A2:A8,"East",B2:B8,"Widget") | COUNTIF cannot express the second condition. |
| Numeric threshold | =COUNTIF(C2:C8,">5") | =COUNTIFS(C2:C8,">5") | Operator syntax is the same. |
| OR across regions | =SUM(COUNTIF(A2:A8,{"East","West"})) | =COUNTIF(A2:A8,"East")+COUNTIF(A2:A8,"West") | Neither has native OR; both use a workaround. |
| Count blanks | =COUNTIF(B2:B8,"") | =COUNTBLANK(B2:B8) | COUNTBLANK is the purpose-built function. |