The Excel COUNTIF function, explained interactively.
COUNTIF walks a range, tests each cell against a single criterion, and returns how many cells pass. No summing, no averaging — the cleanest way to answer “how many?”.
How to use COUNTIF
Three steps — pick a range, pick a criterion, read off the count.
- Type
=COUNTIF(and select the range — the column of values you want to test. - Add a comma and the criteria. For an exact match, drop in a number (
5) or a quoted string ("Paid"). For a comparison, wrap the operator inside the quotes:">=4". - Close the parenthesis. COUNTIF returns an integer — 0 if nothing matches, never an error.
5), comparisons (">=4"), or wildcards ("*berry"). Operators must stay inside quotes.| A | B | |
|---|---|---|
| 1 | Reviewer | Rating |
| 2 | Ava | 5 |
| 3 | Ben | 3 |
| 4 | Chloe | 5 |
| 5 | Dev | 2 |
| 6 | Elena | 4 |
| 7 | Farid | 5 |
| 8 | Grace | 1 |
| 9 | Harvey | 4 |
| 10 | ||
| 11 | COUNTIF ">=4" | 5 |
COUNTIF syntax and arguments
Two positional arguments — a range and one criterion. See Microsoft’s official COUNTIF reference for the canonical specification.
A2:A100), an entire column (A:A), or a rectangular block. Empty cells and cells with errors are skipped.5, "Paid"), comparisons (">100", "<=50"), and wildcards ("*berry", "??red"). Operators must sit inside the quotes. To inject a cell value with an operator, concatenate with &: ">"&B1.COUNTIF examples
Four patterns that cover nearly every COUNTIF you’ll ever write.
Example 1: COUNTIF for an exact match
The canonical case — how many rows have a specific text value?
Counts how many cells in A2:A100 equal Paid. Case-insensitive; leading or trailing spaces count as a mismatch, so TRIM your source data first.
Example 2: COUNTIF with a numeric comparison
Counting scores above a threshold, orders over a value, etc.
Keep the operator inside the quotes. A bare >=100 without quotes is a syntax error. Only the comparison operators (>, <, >=, <=, =, <>) are honoured.
Example 3: COUNTIF with wildcards
Count every cell matching a partial text pattern.
* matches any sequence of characters; ? matches exactly one. To count cellscontaining a substring, wrap it on both sides: "*error*". Wildcards only apply to text ranges.
Example 4: COUNTIF with a dynamic cell reference
Build dashboards where the filter comes from an input cell the user can change.
D1 holds a threshold; & concatenates it after the operator. Without concatenation you’d pass the literal string ">=D1" and count nothing.
Common COUNTIF errors and fixes
Four failure modes, each with what to check and how to recover.
COUNTIF returns 0 unexpectedly
Cause: source values are stored as text instead of numbers, or carry invisible whitespace.
Select the column, run Data → Text to Columns → Finish, then re-evaluate. Or compare lengths: =LEN(A2) vs =LEN(TRIM(A2)) exposes trailing spaces.
COUNTIF with operator outside the quotes
Cause: wrote =COUNTIF(B2:B100, >=100) instead of ">=100".
Quote the entire comparison. For cell-driven thresholds, concatenate the operator with &: ">="&D1.
COUNTIF wildcards matching too much
Cause: forgot that * also matches zero characters, or didn’t escape a literal ? / * in the source text.
Escape literals with a tilde: "~*" matches an actual asterisk. Tighten loose patterns with anchors or length checks.
COUNTIF #VALUE! on mismatched ranges
Cause: the criteria pointed at a different sheet whose reference path broke, or range is a 3D reference.
COUNTIF accepts only 2D ranges. Re-select the range from the active sheet and confirm the reference does not span multiple sheets (3D refs) or contain union separators.
COUNTIF vs COUNTIFS, SUMIF & COUNTA
The counting family: pick one condition or many, count everything or just numbers.
| Function | Conditions | What it counts | Use when |
|---|---|---|---|
| COUNTIF | Exactly one | Rows matching the criterion | One filter, read the count. The everyday tally tool. |
| COUNTIFS | One or many (AND) | Rows matching every criterion | Multi-column filter — e.g. “Food in January”. |
| SUMIF | Exactly one | Sum of a parallel column | Not how many matched, but the total of a linked numeric column. |
| COUNTA | None | Every non-empty cell | Just checking how many rows are populated, regardless of value. |
The rule of thumb: COUNTIF answers how many match, SUMIF answers what they add up to, and COUNTIFS / SUMIFS extend both to multiple conditions. The -IFS variants AND every criterion together; for OR logic, add two calls.
COUNTIF frequently asked questions
6.01How do I count cells with multiple conditions in COUNTIF?▸
You can’t — COUNTIF supports exactly one criterion. Use COUNTIFS for multiple conditions AND’d together, or add two COUNTIFs for OR logic: =COUNTIF(A:A,"Food")+COUNTIF(A:A,"Travel").
6.02Can COUNTIF use wildcards?▸
Yes. * matches any sequence of characters; ? matches a single character. Example: =COUNTIF(A2:A100, "*berry") counts cells ending in berry. Wildcards only work on text ranges.
6.03How do I use a cell reference inside COUNTIF criteria?▸
A bare cell reference works for equality: =COUNTIF(A:A, B1). To combine an operator with a cell, concatenate with &: =COUNTIF(A:A, ">"&B1).
6.04Is COUNTIF case-sensitive?▸
No. COUNTIF treats apple and APPLE as the same. For case-sensitive counting, use SUMPRODUCT with EXACT: =SUMPRODUCT(--EXACT(A2:A100, "Apple")).
6.05Why is COUNTIF returning 0 when I can see matching values?▸
Usually the values are stored as text vs numbers, or there is a trailing space. Try =COUNTIF(A2:A100, "*"&B1&"*") to match with wildcards, or use TRIM to clean the source data.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.