Excel like a finance pro.
Statistical function
COUNTIF
Counts cells that meet one condition, with wildcards allowed in text.
When to use it
Counts the cells that meet one condition. The first function most people learn after SUM and the basis of every duplicate check.
The shape of it
- Syntax
=COUNTIF(range, criteria)
- Example
=COUNTIF(A2:A100,"*inc*")
Worked examples
Count one value
=COUNTIF(C2:C7,"West") → 3
Regions West, East, West, North, West, East.
Count above a threshold
=COUNTIF(G2:G7,">200") → 3
Sales 100, 200, 150, 300, 250, 400: three over 200.
Duplicate flag
=COUNTIF($A$2:$A$100,A2)>1 → TRUE for duplicates
Copied down, flags every value that appears more than once.
Worth knowing
- Criteria are text: ">10", "<>West", "app*". Join cell references with &.
- Running duplicate count: =COUNTIF($A$2:A2,A2) numbers the occurrences 1, 2, 3.
- Wildcards: "?" one character, "*" any run; escape a literal ? with ~?.
- Not case sensitive; use SUMPRODUCT with EXACT if case matters.
Where it goes wrong
- Criteria over 255 characters return #VALUE!.
- Text criteria are matched by cell value, so "1" matches number 1 and text "1" alike, while ">1" matches numbers only.
- A criteria cell that is blank matches blank cells, not everything.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.