COUNTIF Function in Excel
COUNTIF counts cells that meet a single condition. It's the go-to function for "how many of X are there" — how many customers in a region, how many orders over $500, how many blank cells, how many duplicates. Here's every practical use.
What it does: Returns the number of cells in range that meet criteria. Only counts non-blank cells that match; ignores empty cells regardless of criteria.
What COUNTIF does
COUNTIF walks through a range and increments a counter every time a cell matches your criteria. It's the counting equivalent of SUMIF — where SUMIF adds values, COUNTIF just counts occurrences.
Because Excel's counting functions distinguish between numbers, text, blanks, and errors, COUNTIF is also the standard way to check for the presence of something: "does this value exist in this list?" is answered by =COUNTIF(list, value) > 0.
=COUNTIF(A:A, A2) > 1 returns TRUE if the value in A2 appears more than once in column A. Combine with conditional formatting to highlight duplicates in real time.
Syntax breakdown
range Required
The range to check. Can be a single column, single row, or a rectangular block. Blank cells are always excluded from the count regardless of criteria.
criteria Required
The condition each cell must match. Can be a number (50), text in quotes ("Active"), a comparison (">100"), a cell reference (C2), or a wildcard pattern ("*@gmail.com"). Text and comparisons must be inside double quotes.
5 real-world examples
Example 1: Count how many customers are in a region
Column A has region names. Count how many rows are "East":
=COUNTIF(A2:A100, "East")Result: 27 — the number of rows in A2:A100 matching "East" exactly. Case-insensitive.
Example 2: Count values above a threshold
Column B has order amounts. Count orders greater than $500:
=COUNTIF(B2:B100, ">500")Result: 42. The comparison operator must be inside quotes. Use ">=", "<", "<=", or "<>" similarly.
Example 3: Count with a wildcard for partial match
Column C has email addresses. Count Gmail users:
=COUNTIF(C2:C1000, "*@gmail.com")Result: 213. The * matches any characters before @gmail.com. Wildcards only work with text criteria in quotes.
Example 4: Find duplicates in a list
Flag any customer ID that appears more than once. In cell D2, next to customer ID A2:
Result: Shows "Duplicate" next to every row whose ID appears more than once. The dollar signs anchor the range so the formula can be filled down.
Example 5: Count blank cells (or non-blanks)
Count how many cells in column B are empty:
=COUNTIF(B2:B100, "")Result: Count of empty cells. To count non-empty: =COUNTIF(B2:B100, "<>"). Note: COUNTBLANK and COUNTA are more idiomatic for this — see the comparison table.
Common errors and how to fix them
Rare with COUNTIF — usually caused by referencing a closed workbook in the range. Fix: open the referenced workbook or copy the data locally.
Most common: trailing spaces in source data ("East " won't match "East"). Fix: use TRIM to clean the data, or match with wildcards: =COUNTIF(A:A, "East*").
Wildcards may be matching more than intended. "East*" matches "East", "Easter", "Eastside", "East Region", etc. Use exact match if that's not what you want: drop the asterisk.
If your criteria is a number like =COUNTIF(A:A, 100) but the cells contain "100" as text, they won't match. Convert with VALUE or multiply the source by 1 in a helper column.
COUNTIF vs COUNTIFS vs COUNTA vs COUNTBLANK
| Function | What it counts | Use when |
|---|---|---|
| COUNTIF | Cells matching one condition | Simple filtering by a single criterion |
| COUNTIFS | Cells matching multiple AND conditions | Multi-column filtering (e.g., region AND status) |
| COUNTA | Any non-empty cell | Counting how many rows have data |
| COUNTBLANK | Empty cells only | Finding gaps in data |
| COUNT | Cells with numbers only | Skipping text and blanks |
Version compatibility
Download the practice workbook
Every example above, plus duplicate-detection templates and dashboard patterns.
Related functions
Frequently asked questions
Is COUNTIF case-sensitive?
No. =COUNTIF(A:A, "east") matches "East", "EAST", and "east". For case-sensitive counting, use SUMPRODUCT with EXACT: =SUMPRODUCT(--EXACT(A2:A100, "east")).
Can COUNTIF count cells across multiple sheets?
Not directly. Add COUNTIFs from each sheet: =COUNTIF(Sheet1!A:A, "East") + COUNTIF(Sheet2!A:A, "East"). For many sheets, consolidate data first with Power Query.
How do I count unique values?
Not with COUNTIF directly. In modern Excel: =COUNTA(UNIQUE(A2:A100)). In older versions: =SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)). See the UNIQUE function page for details.
Why doesn't COUNTIF count my numbers?
Usually because they're stored as text. Look for a green triangle in the corner of the cells — that indicates "number stored as text". Convert with VALUE(), or select the range, click the warning icon, and choose "Convert to Number".
Can I use OR conditions in COUNTIF?
Not in one call. Add two COUNTIFs: =COUNTIF(A:A, "East") + COUNTIF(A:A, "West"). Careful — if a cell could match both criteria (which for exact-text matches it can't, but for wildcards it can), you'd double-count. Then use SUMPRODUCT.
What's the difference between COUNTIF and FILTER + COUNTA?
Same result in most cases, different approach. =COUNTIF(A:A, "East") counts directly. =COUNTA(FILTER(A:A, A:A="East")) filters first, then counts. COUNTIF is faster and older. FILTER is more flexible but requires Excel 365 / 2021+.
How do I count cells that contain specific text anywhere in the string?
Use wildcards around the text: =COUNTIF(A:A, "*urgent*") counts cells containing "urgent" anywhere. Without the asterisks it only matches cells equal to exactly "urgent".
Get instant counts and duplicate checks — with the Sheets & Cells AI Add-in
Describe what you want to count in plain English and our Excel Add-in generates the right COUNTIF, COUNTIFS, or COUNTA — plus flags duplicates in your data automatically. All inside Excel.
Learn about the Add-in →