Head-to-head · Statistics

COUNTIF vs COUNTIFS

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.

fx
COUNTIF(…)  vs  COUNTIFS(…)
12
capabilities compared
7
scenarios measured live
127
criteria pairs in COUNTIFS
▶ Try both live See the feature matrix
🏆Verdict: COUNTIF for one condition; COUNTIFS for two or more

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.

COUNTIF strengths

  • Simpler two-argument syntax for a single test
  • One criteria range, one condition — easy to read
  • Ideal for quick counts, flags and dashboards

COUNTIFS strengths

  • Counts rows matching several conditions at once (AND)
  • Up to 127 criteria pairs, each on its own range
  • Same operator and wildcard syntax as COUNTIF

Feature-by-feature matrix

Every row is a real capability difference. Operators, wildcards and case-insensitivity are identical; condition count and argument structure are where they diverge.

CapabilityCOUNTIFCOUNTIFSWhy it matters
Conditions supported✓ Exactly 1✓ 1 to 127The defining difference.
Argument order✓ range, criteria✓ range1, criteria1, …COUNTIFS pairs each range with its criteria.
Comparison operators✓ >, <, >=, <>, =✓ >, <, >=, <>, =Identical; quoted, e.g. ">5".
Wildcards (* ?)✓ Yes✓ YesBoth match text patterns; ~ escapes.
Case sensitivity✓ Case-insensitive✓ Case-insensitive"east" matches "East" in both.
Multiple criteria ranges✗ One range✓ One per conditionCOUNTIFS can test different columns.
AND logic✗ Not applicable✓ NativeAll COUNTIFS conditions must hold.
OR logic◐ SUM + array constant◐ Add separate callsNeither 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:ABoth accept them; bounded ranges are faster.
Excel version✓ All versions✓ 2007+COUNTIFS arrived in Excel 2007.

Syntax, side by side

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.

COUNTIF

COUNTIF(range, criteria)
rangeThe cells to test against the criteria.
criteriaWhat to match: a number, text, ">5", a wildcard, or a cell reference.
=COUNTIF(A2:A8, "East")

COUNTIFS

COUNTIFS(range1, criteria1, [range2, criteria2], …)
range1The first cells to test.
criteria1The condition applied to range1.
range2, criteria2Optional extra pairs, up to 127. All must hold (AND).
=COUNTIFS(A2:A8, "East", B2:B8, "Widget")

Live head-to-head playground

Type your own COUNTIF and COUNTIFS formulas against the live grid and watch the count update.

A1Sample data — click any cell to edit; both formulas re-evaluate live

COUNTIF

—

COUNTIFS

—
HyperFormula 3.5 · live

Every difference, measured

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.

Single condition — the baseline tie

Count rows where Region is East.
COUNTIF
=COUNTIF(A2:A8,"East")
4
COUNTIFS
=COUNTIFS(A2:A8,"East")
4
Both return 4. For one condition COUNTIF and COUNTIFS are identical — COUNTIF is just shorter to type.

Two conditions — where they split

Count rows where Region is East AND Product is Widget. COUNTIF has no second criteria argument.
COUNTIF
=COUNTIF(A2:A8,"East")
4
COUNTIFS
=COUNTIFS(A2:A8,"East",B2:B8,"Widget")
2
COUNTIF can only count East (4); it cannot also filter Product. COUNTIFS returns 2 (East AND Widget). This is the headline difference.

Numeric operator criteria

Count rows where Qty is greater than 5.
COUNTIF
=COUNTIF(C2:C8,">5")
4
COUNTIFS
=COUNTIFS(C2:C8,">5")
4
Both return 4 (10, 8, 7, 6). Quote the operator: ">5". Identical criteria syntax in both.

Wildcard match

Count products whose name starts with W.
COUNTIF
=COUNTIF(B2:B8,"W*")
4
COUNTIFS
=COUNTIFS(B2:B8,"W*")
4
Both return 4 (the Widget rows). * matches any run of characters, ? matches one. Same in both.

OR logic across values

Count rows where Region is East OR West.
COUNTIF
=SUM(COUNTIF(A2:A8,{"East","West"}))
6
COUNTIFS
=COUNTIFS(A2:A8,"East")+COUNTIFS(A2:A8,"West")
6
Both return 6. COUNTIF with an array constant inside SUM does OR in one formula; COUNTIFS is AND-only, so add two calls. (East has 4 rows, West has 2.)

Counting blank cells

Count empty Score cells. Both use the empty-string criteria; COUNTBLANK is the dedicated alternative.
COUNTIF
=COUNTIF(B2:B6,"")
2
COUNTIFS
=COUNTIFS(B2:B6,"")
2
Both return 2. COUNTIF(range,"") and COUNTIFS(range,"") count truly blank cells; COUNTBLANK(range) is the purpose-built function for this single job. (Cells must be genuinely empty, not empty-string text.)

Not-equal criteria

Count rows where Region is NOT East.
COUNTIF
=COUNTIF(A2:A8,"<>East")
3
COUNTIFS
=COUNTIFS(A2:A8,"<>East")
3
Both return 3 (West ×2, North ×1). The <> operator means 'not equal'; quote it as "<>East".

Engine benchmark

Same dataset, same engine, same machine — relative cost only. Both are fast; COUNTIFS does slightly more work per row as conditions are added.

Table sizeCOUNTIFCOUNTIFS
1,000 rows × 500 lookups331.2 ms257.41 ms
5,000 rows × 500 lookups1985.5 ms1995.27 ms
10,000 rows × 500 lookups5919.7 ms5845.74 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Absolute times are engine-specific; use only as a relative signal. Real Excel performance depends on version, data layout and calculation chain.

Which one should you use?

Match your situation to a recommendation. The rule of thumb: one condition → COUNTIF; two or more → COUNTIFS.

SituationUseWhy
Counting cells that match a single conditionCOUNTIFTwo arguments, nothing extra to manage.
Counting rows that must match two or more conditionsCOUNTIFSAND across conditions is built in.
Each condition lives in a different columnCOUNTIFSOne criteria_range per condition.
You want a numeric threshold like >5 or <=100COUNTIFOne condition — operators work the same in both.
You need OR (this or that)COUNTIFSUM(COUNTIF(range,{"a","b"})) is the compact idiom.
Counting blank cells in one rangeCOUNTIFcriteria "" works, though COUNTBLANK is clearer.
Building a summary table broken down by several fieldsCOUNTIFSRegion + product + date in one formula.
Supporting workbooks older than Excel 2007COUNTIFCOUNTIFS does not exist before 2007.

From COUNTIF to COUNTIFS migration

Common COUNTIF tasks rewritten in COUNTIFS. Single-condition formulas are unchanged; the gain appears when you add conditions.

TaskCOUNTIF / old wayCOUNTIFSWhat 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.

Frequently asked questions

What is the difference between COUNTIF and COUNTIFS?
COUNTIF counts cells matching one condition (range, criteria). COUNTIFS counts rows matching up to 127 conditions, each with its own range, combined with AND. Operators and wildcards are identical.
Can COUNTIF handle two conditions?
Not directly. You can nest or add COUNTIF calls, or use SUMPRODUCT, but COUNTIFS is the clean way to apply several conditions at once.
Do COUNTIF and COUNTIFS support wildcards?
Yes. Both accept * (any characters) and ? (one character) in text criteria, and ~ to escape a literal wildcard. Numeric criteria ignore wildcards.
How do I do OR logic with COUNTIFS?
COUNTIFS is AND-only. For OR, add separate COUNTIF/COUNTIFS calls, or use SUM(COUNTIF(range,{"a","b"})) with an array constant.
Are COUNTIF and COUNTIFS case-sensitive?
No. Both treat "east" and "East" as equal. For case-sensitive counting, use SUMPRODUCT with EXACT.
How do I count blank cells?
COUNTIF(range,"") or COUNTIFS(range,"") count genuinely empty cells. COUNTBLANK(range) is the dedicated function for a single range. Cells containing an empty string ("") are not counted as blank.
Which is faster, COUNTIF or COUNTIFS?
Both are fast. In our HyperFormula benchmark they were within a few percent at every size; COUNTIFS does marginally more work per extra condition. Prefer bounded ranges over whole columns for speed.