How to Use COUNTIF in Excel
COUNTIF counts cells in a range that meet a condition — text, numbers, dates, or wildcards. Here's the syntax, examples for common use cases, and when to use COUNTIFS instead.
COUNTIF counts how many cells in a range meet a condition you specify. It's the answer to questions like "how many orders are from California?", "how many values are over 100?", or "how many rows have a blank in this column?".
The syntax
=COUNTIF(range, criteria)- range — the cells to check
- criteria — what to count for. Can be text, a number, a comparison, a wildcard, or a cell reference
Common use cases
Count by exact text match:
=COUNTIF(A2:A100, "California")Count numbers meeting a threshold:
=COUNTIF(B2:B100, ">1000")Count non-blank cells:
=COUNTIF(A2:A100, "<>")Count cells containing specific text (wildcard):
=COUNTIF(A2:A100, "*Corp*")The * wildcard matches any sequence of characters. Use ? to match exactly one character.
Count using a cell reference as criteria:
=COUNTIF(A2:A100, D2)✦ TIP
To count duplicates, use =COUNTIF($A$2:$A2, A2) with an expanding range. Any result greater than 1 is a duplicate. See the remove duplicates guide for the full pattern.
COUNTIF is case-insensitive
"apple", "Apple", and "APPLE" all count as the same. If case matters, use EXACT inside SUMPRODUCT:
=SUMPRODUCT((EXACT(A2:A100, "Apple"))*1)Multiple conditions: COUNTIFS
COUNTIF only handles one condition. For two or more, use COUNTIFS:
=COUNTIFS(A2:A100, "California", B2:B100, ">1000")COUNTIFS uses the same pattern as SUMIFS — alternate between range and criteria pairs.
Common mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Forgetting quotes around text | #NAME? error | Wrap text in quotes: "California" |
| Forgetting quotes around operators | Counts wrong | Use ">1000" not >1000 |
Using = instead of "" for blanks | Misses some blanks | Use "" for blanks, "<>" for non-blanks |
The Griddy way
COUNTIF is easy for simple counts but awkward for partial matches, case sensitivity, or OR logic across multiple columns. Just ask:
"Count how many rows have 'Corp' anywhere in the company name and a deal value over $5,000"
Griddy writes the formula and applies it — no manual syntax construction.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.