COUNTIF vs COUNTIFS: One Condition or Many

Excel & Google Sheets

How it works

COUNTIF takes exactly one range and one condition: =COUNTIF(A2:A50,"East") counts East orders and that's all it can do. COUNTIFS takes range/condition pairs — as many as you need — and only counts rows where every pair matches, so the formula above counts orders that are East AND over $1,000. With a single pair, COUNTIFS gives exactly the same answer as COUNTIF, which is why a common piece of advice is to just always use COUNTIFS: you never rewrite the formula when a second condition shows up. Both functions accept the same condition syntax — text, ">1000", "<>", and wildcards like "A*".

A2:A50,"East"

First pair: the region column and the value it must equal.

B2:B50,">1000"

Second pair: the amount column and its condition. Operators go inside quotes.

When to use it

Use COUNTIFS for any conditional count in an order log, pipeline, or invoice list — one condition or five. COUNTIF only earns its place inside legacy formulas you're maintaining.

Common mistakes

• ✕Stuffing a second condition into COUNTIF.

  ✓=COUNTIF(A2:A50,"East",B2:B50,">1000") is an error — COUNTIF stops at one condition. Switch the name to COUNTIFS; the pairs are already in the right order.

• ✕Criteria ranges of different sizes.

  ✓COUNTIFS(A2:A50,"East",B2:B999,">1000") returns #VALUE!. Every range must cover exactly the same rows.

• ✕Expecting OR logic from the pairs.

  ✓COUNTIFS conditions are AND — every one must hold. For East OR West, add two counts: =COUNTIF(A2:A50,"East")+COUNTIF(A2:A50,"West").