How to Use COUNTIF in Google Sheets (With Examples)
COUNTIF counts the cells that meet one condition. The syntax is =COUNTIF(range, criterion) — so =COUNTIF(A2:A100, "North") counts how many cells in column A say “North”. When you need more than one condition, use COUNTIFS.
The two arguments
| Argument | What it is | Example |
|---|---|---|
range | The cells to check | A2:A100 |
criterion | What to match | "North" |
That’s the whole function. The criterion is where the flexibility — and the confusion — lives.
Worked examples
Count text matches:
=COUNTIF(A2:A100, "North")Count numbers above a threshold:
=COUNTIF(B2:B100, ">500")Count blank cells:
=COUNTIF(A2:A100, "")Count non-blank cells:
=COUNTIF(A2:A100, "<>")Count cells containing part of a word (wildcard):
=COUNTIF(A2:A100, "*west*")The criteria rules worth memorising
This is where most COUNTIF mistakes happen:
| To match | Write it as |
|---|---|
| Exactly “North” | "North" |
| Greater than 500 | ">500" |
| Greater than or equal to 500 | ">=500" |
| Not equal to “North” | "<>North" |
| Blank | "" |
| Not blank | "<>" |
| Starts with “Nor” | "Nor*" |
| Ends with “west” | "*west" |
| Contains “orth” | "*orth*" |
| A value in another cell | ">"&E2 |
Operators go inside the quotes. Cell references go outside. That last row — ">"&E2 — is the trick for a condition driven by a cell rather than typed in.
Case sensitivity: COUNTIF is not case-sensitive. “north” and “North” both count. (For case-sensitive counting you’d need SUMPRODUCT with EXACT.)
COUNTIFS: several conditions at once
COUNTIFS takes pairs — each range followed by its criterion:
=COUNTIFS(A2:A100, "North", C2:C100, "Q1")That counts rows where column A is “North” and column C is “Q1”. Add more pairs for more conditions — up to 127.
All conditions must be true for a row to count (it’s AND, not OR). To count either of two conditions, add two COUNTIFs together:
=COUNTIF(range,"North") + COUNTIF(range,"South").
Counting vs. summing
These three functions get mixed up constantly:
| Formula | What it does |
|---|---|
| COUNTIF | Counts cells matching a condition |
| SUMIF | Adds up cells matching a condition |
| COUNTA | Counts non-empty cells, no condition |
So “how many sales in the North region” is COUNTIF; “what’s the total value of North sales” is SUMIF. If you need the summing version, see how to use SUMIF in Excel — the logic is the same.
A useful pattern: count and total together
To show “12 of 40 items are complete”:
=COUNTIF(B2:B41, "Done") & " of " & COUNTA(B2:B41)That combines a count with a total into one readable cell, and it updates as the data changes. Pair it with checkboxes — see how to add checkboxes in Google Sheets — and it becomes a live progress line.
Counting unique values
COUNTIF counts occurrences, not distinct values. To count unique entries:
=COUNTUNIQUE(A2:A100)Or use =SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)) for a count of distinct values in a range containing blanks-free data.
Common problems
| Problem | Fix |
|---|---|
| Returns 0 when there are matches | Often a trailing space, or a number stored as text. Try =COUNTIF(ARRAYFORMULA(TRIM(A2:A100)), "North") |
#ERROR! or a formula error | The quotes are missing — text criteria need "like this" |
| Numbers not counting | The cells hold text, not numbers. Left-aligned values are usually text |
| Only some rows counted | The range stops short of the data |
Criteria with a > doesn’t work | The operator must be inside the quotes: ">500", not > "500" |
| COUNTIFS counts too few | All conditions must match — check each range covers the same rows |
FAQ
What does COUNTIF do in Google Sheets?
It counts the cells in a range that match one condition. =COUNTIF(A2:A100, "North") counts how many say “North”.
What’s the difference between COUNTIF and COUNTIFS?
COUNTIF takes one condition; COUNTIFS takes several, and a row must match all of them to be counted.
How do I count cells greater than a number?
Put the operator inside the quotes: =COUNTIF(B2:B100, ">500"). To use a cell value instead, write =COUNTIF(B2:B100, ">"&E2).
Is COUNTIF case sensitive?
No. “North” and “north” both count. Case-sensitive counting needs SUMPRODUCT with EXACT.
How do I count cells that contain specific text?
Use wildcards: =COUNTIF(A2:A100, "*north*") counts any cell containing “north” anywhere. "north*" matches only cells starting with it.
Related guides
Google Sheets Formulas for Beginners (10 You Need to Know)
The 10 essential Google Sheets formulas every beginner needs — SUM, AVERAGE, COUNT, IF, and more, with working examples.
How to Add Checkboxes in Google Sheets (Tick Boxes)
Add checkboxes in Google Sheets with Insert → Checkbox, set custom checked/unchecked values, and count ticks with COUNTIF or a progress bar.
How to Create a Drop-Down List in Excel (Data Validation)
Add a drop-down list in Excel with Data Validation — pick from a typed list or a range on another sheet, and fix the errors that stop it working.