COUNTIF? Plain Basics
COUNTIF is the Google Sheets function that counts how many cells meet a condition — how many 'yes' votes, how many sales over $100, how many blanks. One formula replaces manual counting forever. This guide shows the syntax, the comparison tricks, and the blanks quirk that trips everyone up.
TL;DR / Quick Fix
Quick fix: COUNTIF counts cells matching a condition — =COUNTIF(A2:A100, 'yes') tallies every 'yes'. Use comparison operators in quotes like '>10' for numbers. It counts exact text matches; extra spaces break it. Pair with UNIQUE to count distinct values instead.
Follow these steps
- Learn the basic syntax. =COUNTIF(range, criterion) — for example, =COUNTIF(A2:A100, 'yes') counts every cell containing exactly 'yes'. The criterion goes in quotes for text.
- Count with comparisons. For numbers, put the operator in quotes: =COUNTIF(B2:B100, '>10') counts values over 10. '>=', '<=', '<>' (not equal) all work the same way.
- Use wildcards. '*' matches any text: =COUNTIF(A2:A100, 'A*') counts everything starting with A. '?' matches a single character — handy for pattern counting.
- Mind the blanks quirk. COUNTIF treats truly empty cells and formula-empty cells differently — the blanks quirk guide explains the trap that miscounts empty-looking cells.
- Count distinct with UNIQUE. To count how many different values exist, combine: =COUNTUNIQUE(A2:A100). Or pair COUNTIF with the UNIQUE function for 'how many of each' breakdowns.
- Step up to COUNTIFS. COUNTIFS (with an S) takes multiple conditions: =COUNTIFS(A2:A100, 'yes', B2:B100, '>10'). Same idea, more filters — the natural next step.
What NOT to do
- Do not forget the quotes. Text criteria and operators need quotes ('yes', '>10'); unquoted text becomes a cell reference and silently counts wrong.
- Do not expect case sensitivity. COUNTIF treats 'Yes' and 'yes' as the same — usually what you want, but know it before relying on case.
- Do not count formatted illusions. A cell displaying '$10' may hold the number 10 — COUNTIF sees the value, not the formatting. Match criteria to values.
- Do not use COUNTIF on merged cells casually. Merged cells confuse ranges — unmerge first or the counts land in surprising places.
Frequently asked questions
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes one condition; COUNTIFS takes several that must all be true. Start with COUNTIF, graduate to COUNTIFS when you need AND logic.
Why is my COUNTIF returning 0?
Usually quotes missing around the criterion, extra spaces in the data, or numbers stored as text. Check all three in that order.
Can COUNTIF count across sheets?
Yes — reference the other sheet: =COUNTIF(Sheet2!A2:A100, 'yes'). Cross-sheet counting works exactly like same-sheet.