COUNTIF and COUNTIFS count cells that meet conditions — COUNTIF for a single condition, COUNTIFS for multiple. They're essential for any kind of tallying, deduplication checking, or conditional reporting.
COUNTIF: one condition
=COUNTIF(range, criteria)
- range — the cells to check.
- criteria — the condition to count.
Example — count how many times "East" appears in column A:
=COUNTIF(A2:A100, "East")
Criteria options
The criteria work just like SUMIF:
- Text:
"East" - Numbers:
100 - Comparisons:
">100","<=50","<>0" - Wildcards:
"East*"(starts with),"*North*"(contains),"?at"(single-character wildcard) - Cell references:
">"&C1
COUNTIFS: multiple conditions
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
Example — count rows where region is "East" AND sales are over 100:
=COUNTIFS(A2:A100, "East", B2:B100, ">100")
All conditions must be true for a row to count (AND logic). Note that unlike SUMIFS, COUNTIFS has no separate "count range" — it just counts rows where all criteria match, so the argument order is simply range/criteria pairs throughout.
Common uses
Find duplicates. Count how many times each value appears; anything greater than 1 is a duplicate:
=COUNTIF(A:A, A2)
Drag this down and any result above 1 flags a repeated value.
Check if a value exists. COUNTIF returns 0 if not found, so you can test existence:
=IF(COUNTIF(A:A, "East")>0, "Yes", "No")
Count non-blank conditionally, tally survey responses, count items in a category — anywhere you need "how many meet this rule."
Counting text vs blanks
- Count non-empty cells:
=COUNTA(range) - Count empty cells:
=COUNTBLANK(range) - Count cells containing any text:
=COUNTIF(range, "*")
The takeaway
COUNTIF and COUNTIFS follow the exact same criteria logic as SUMIF/SUMIFS — learn one set and the other comes free. For anything more complex than a single tally, COUNTIFS with multiple criteria pairs handles it cleanly.