COUNTIF — Count by One Condition
Excel's original conditional-count function. Point it at a range, give it one condition, get back the number of cells that match. Simpler than SUMIF (just two arguments) and the go-to tool for duplicate detection, category tallies, and quick sanity checks. Also universally supported since Excel 1.0.
_xlfn. prefix. Wildcards (* and ?) work everywhere. Ship anywhere — COUNTIF just works.S to the function name.range, criteria pairs for additional conditions (AND logic).Syntax breakdown
COUNTIF is the simplest of the *IF family — just two required arguments. No optional third argument like SUMIF or AVERAGEIF.
| Argument | Type | What it does |
|---|---|---|
range |
REQUIRED | The range where the criteria will be evaluated. Every cell here is tested against the criteria. Text values, numbers, dates all work. |
criteria |
REQUIRED | What to look for. Can be a literal ("Emma Thompson", 5), a comparison expression in quotes (">10000", "<>North"), a cell reference (B2), or a wildcard pattern ("Widget*"). |
COUNTIF(range, "").
Five working examples
Every example pulls from the 18-row Q1 2026 sales log in the workbook. Named ranges: SalesRep, SalesProduct, SalesRegion, SalesAmount, SalesDate.
01 Exact match — count records per category
The most common COUNTIF use. "How many sales did Emma make?" "How many Widget A units did we sell?"
| Rep | COUNTIF formula | Sale count |
|---|---|---|
| Emma Thompson | =COUNTIF(SalesRep, "Emma Thompson") | 4 |
| David Kim | =COUNTIF(SalesRep, "David Kim") | 4 |
| Sofia Rodriguez | =COUNTIF(SalesRep, "Sofia Rodriguez") | 3 |
| Michael Chen | =COUNTIF(SalesRep, "Michael Chen") | 3 |
| Aisha Patel | =COUNTIF(SalesRep, "Aisha Patel") | 2 |
| James Wilson | =COUNTIF(SalesRep, "James Wilson") | 2 |
Add them up: 4+4+3+3+2+2 = 18. That matches =COUNTA(SalesRep), the total records. Perfect sanity check — if the sum of category counts doesn't match the total, you have data problems.
02 Wildcards — partial matching for pattern counts
Count anything matching a pattern — all "Widget" products, names containing "Kim", codes starting with a specific prefix.
Contains-pattern search
| Pattern | Interpretation | Matches |
|---|---|---|
| "Widget A" | Exact match | Only "Widget A" cells |
| "Widget*" | Starts with "Widget" | Widget A, Widget B |
| "*Widget" | Ends with "Widget" | SuperWidget, MegaWidget |
| "*Widget*" | Contains "Widget" | All of the above |
| "Widget ?" | Widget + space + 1 char | Widget A, Widget B |
TEXT(). Or use comparison operators for numeric ranges — that's what Example 3 covers.
03 Comparison operators — numeric threshold counts
Count how many cells meet a numeric threshold. For "between X and Y" ranges you'll need COUNTIFS.
"Between X and Y" — need COUNTIFS
Why can't COUNTIF do this? A range needs TWO conditions on the SAME column (>=3000 AND <=6000). COUNTIF is single-condition only. This is the primary reason to keep COUNTIFS in mind — even for simple ranges on one column.
Dynamic thresholds with cell references
& concatenates the operator with the cell value.& concatenation is essential. You cannot write ">=B2" — COUNTIF would look for the literal text ">=B2" and find nothing. Build the criteria string with &: ">="&B2 becomes ">=5000" when B2 contains 5000.
04 🎯 Duplicate detection — COUNTIF's killer use case
The single most common real-world COUNTIF pattern. Flag rows that appear more than once, count duplicate occurrences, or drive conditional formatting.
Is this a duplicate?
Readable duplicate flag
| Cell (B2) | COUNTIF(SalesRep, B2) | > 1? | Flag |
|---|---|---|---|
| Emma Thompson | 4 | TRUE | DUPLICATE |
| David Kim | 4 | TRUE | DUPLICATE |
| Sofia Rodriguez | 3 | TRUE | DUPLICATE |
| Aisha Patel | 2 | TRUE | DUPLICATE |
| Solo Person | 1 | FALSE | unique |
| Missing Name | 0 | FALSE | unique |
First-occurrence-only flag
Sometimes you want to flag ONLY the duplicates AFTER the first — leaving the "original" unmarked. This is the classic "keep the first, flag the rest" pattern:
Conditional formatting to highlight duplicates
Same idea, wrapped in a conditional format rule instead of a formula:
- Select the range (e.g.,
B2:B100) - Home → Conditional Formatting → New Rule → Use a formula
- Formula:
=COUNTIF($B$2:$B$100, B2)>1 - Pick a fill color → OK
UNIQUE() available, COUNTIF remains the go-to for flagging duplicates in place without restructuring your data.
05 Counting unique values — the SUMPRODUCT trick
"How many DIFFERENT reps appear in this column?" A surprisingly deep question that COUNTIF alone can't answer — but COUNTIF combined with SUMPRODUCT can.
How the trick works
COUNTIF is called with the range as BOTH arguments. For each cell, it returns how many times that value appears. Emma Thompson (appearing 4 times) returns 4 for every one of her rows. Sofia (3 times) returns 3 for each of her rows. Then 1 / COUNTIF(...) flips those: Emma's rows contribute 1/4 + 1/4 + 1/4 + 1/4 = 1. Sofia's rows contribute 1/3 + 1/3 + 1/3 = 1. Every distinct rep contributes exactly 1 to the sum. SUMPRODUCT adds them up → the unique count.
| Rep | Occurrences | 1 / count contribution |
|---|---|---|
| Emma Thompson (4 rows) | 4 | 4 × (1/4) = 1 |
| David Kim (4 rows) | 4 | 4 × (1/4) = 1 |
| Sofia Rodriguez (3 rows) | 3 | 3 × (1/3) = 1 |
| Michael Chen (3 rows) | 3 | 3 × (1/3) = 1 |
| Aisha Patel (2 rows) | 2 | 2 × (1/2) = 1 |
| James Wilson (2 rows) | 2 | 2 × (1/2) = 1 |
| SUMPRODUCT total → | 6 | |
Modern alternative — Excel 365
Interactive playground
Try it Live COUNTIF demonstration
Mirrors live cells from the workbook. Edit the yellow input → the blue answer updates.
Download the workbook to try COUNTIF patterns across the full 18-row sales log.
The COUNT family — pick the right one
Excel has FIVE count functions. Getting the right one for your task saves confusion later.
Meet the family
Each counts something slightly different. Match the function to what you're actually asking about.
Plus COUNTIFS — multi-condition version of COUNTIF, the fifth member.
COUNTIF vs COUNTIFS — the same question, both ways
Same argument order. Just add the "S".
This is the easiest legacy→modern migration in Excel. First-condition arguments are identical. COUNTIFS adds more range, criteria pairs after.
| Question | Legacy — COUNTIF | Modern — COUNTIFS |
|---|---|---|
| Sales by Emma Thompson |
=COUNTIF(SalesRep, "Emma Thompson")
→ 4
|
=COUNTIFS(SalesRep, "Emma Thompson")
→ 4
|
| Widget A sales |
=COUNTIF(SalesProduct, "Widget A")
→ 5
|
=COUNTIFS(SalesProduct, "Widget A")
→ 5
|
| North region sales |
=COUNTIF(SalesRegion, "North")
→ 4
|
=COUNTIFS(SalesRegion, "North")
→ 4
|
| All "Widget" products |
=COUNTIF(SalesProduct, "Widget*")
→ 9
|
=COUNTIFS(SalesProduct, "Widget*")
→ 9
|
| Sales > $10,000 |
=COUNTIF(SalesAmount, ">10000")
→ 3
|
=COUNTIFS(SalesAmount, ">10000")
→ 3
|
| Emma's Widget A sales (2 conditions) |
COUNTIF cannot do this — only one condition
|
=COUNTIFS(SalesRep, "Emma Thompson", SalesProduct, "Widget A")
→ 1
|
The pattern: unlike SUMIF/SUMIFS where sum_range moves, COUNTIF's (range, criteria) and COUNTIFS's first-pair (range, criteria) are byte-identical. Just add the "S" and you're done. Only difference: COUNTIFS lets you keep adding pairs for AND-logic conditions.
Common errors and how to fix them
| Result | Why it happens | Broken → Fix |
|---|---|---|
| Returns 0 | Criteria text doesn't match — often whitespace, wrong case (in some locales), or a leading/trailing space in source data. | =COUNTIF(SalesRep, "Emma ") // trailing space breaks match
=COUNTIF(SalesRep, TRIM("Emma Thompson")) |
| Wrong number | Wildcards in a numeric range don't work. | =COUNTIF(SalesAmount, "5*") // wildcards don't apply to numbers
=COUNTIF(SalesAmount, ">=5000") |
| Cell ref as text | Comparison operator inside quotes with a cell reference — needs & concatenation. |
=COUNTIF(SalesAmount, ">=B2") // literal ">=B2"
=COUNTIF(SalesAmount, ">="&B2) |
| Duplicates missed | Duplicate check uses >=1 instead of >1 — flags EVERY row. |
=IF(COUNTIF(range, B2) >= 1, "DUP", "") // marks all
=IF(COUNTIF(range, B2) > 1, "DUP", "") |
| DIV/0 error | SUMPRODUCT unique-count trick against a range with blanks — some cells return COUNTIF=0. | =SUMPRODUCT(1/COUNTIF(range, range)) // errors if blanks
=SUMPRODUCT((range<>"")/COUNTIF(range, range&"")) |
| Text-number mismatch | Numeric column stored as text — COUNTIF matches on string form. | Numbers stored as text don't match numeric criteria
Fix source with Text-to-Columns or VALUE() |
Related functions
Complementary functions
Excel version compatibility
COUNTIF has been in Excel since version 1.0 (1985). Every platform supports it identically:
| Platform | Supports COUNTIF? | Notes |
|---|---|---|
| Excel 365 (Windows & Mac) | ✓ Yes | Full support |
| Excel 2021 / 2019 / 2016 / 2013 / 2010 / 2007 / 2003 | ✓ Yes | Full support |
| Excel for the web | ✓ Yes | Full support |
| Excel on iPad & iPhone | ✓ Yes | Full support |
| Google Sheets | ✓ Yes | Same syntax |
| LibreOffice Calc | ✓ Yes | Full support |
| Apple Numbers | ✓ Yes | Full support |
How to write a COUNTIF from scratch
-
Identify the range you want to check
Which column or range holds the values you're testing? That's the first argument.
-
Write the criteria
Exact text:
"Emma Thompson". Number comparison:">10000". Wildcard:"Widget*". Cell reference:B2. Cell reference with operator:">="&B2. -
For duplicate detection, compare the result to 1
=COUNTIF(range, cell) > 1returns TRUE for duplicates. Wrap in IF for a readable label. -
For unique counts, use SUMPRODUCT + COUNTIF
=SUMPRODUCT(1/COUNTIF(range, range)). Each unique value contributes exactly 1 to the sum. -
Need 2+ conditions → COUNTIFS
Same argument order as COUNTIF for the first pair. Just add more
range, criteriapairs for AND logic.
Functions used with COUNTIF
Frequently asked questions
What's the difference between COUNTIF and COUNTIFS?
COUNTIF handles ONE condition; COUNTIFS handles multiple. Unlike SUMIF/SUMIFS, the argument order is the SAME for the first pair — just add the "S" and you can add more range, criteria pairs for AND-logic across multiple columns.
How do I detect duplicates with COUNTIF?
=COUNTIF(range, cell) > 1 returns TRUE for values appearing more than once. Wrap in IF for a readable label: =IF(COUNTIF(range, B2) > 1, "DUPLICATE", "unique"). Or use it in a conditional formatting rule to highlight duplicates visually.
How do I count unique values?
The classic trick: =SUMPRODUCT(1 / COUNTIF(range, range)). Each unique value contributes exactly 1 to the sum, so the result is the distinct count. On Excel 365, =COUNTA(UNIQUE(range)) is cleaner but not backward-compatible.
Do wildcards work in COUNTIF?
Yes — * matches any sequence of characters, ? matches exactly one. Only in TEXT criteria, not numeric ranges. COUNTIF(SalesProduct, "Widget*") counts all products starting with "Widget".
How do I count "between X and Y"?
COUNTIF can't do this alone — it only handles ONE condition. Either use COUNTIFS with two conditions: =COUNTIFS(range, ">=X", range, "<=Y"), or subtract two COUNTIFs: =COUNTIF(range, "<=Y") - COUNTIF(range, "<X").
Why is my COUNTIF returning zero?
Most common: trailing whitespace in the source data ("Emma " vs "Emma"), numbers stored as text (won't match numeric criteria), or wildcards used on a numeric range.
Is COUNTIF case-sensitive?
No. COUNTIF treats "Emma", "emma", and "EMMA" as identical. For case-sensitive counting, use SUMPRODUCT with EXACT: =SUMPRODUCT(--EXACT(range, "Emma")).
Does COUNTIF work in Google Sheets?
Yes, identically. Same syntax across Google Sheets, LibreOffice, and Apple Numbers. Wildcards work the same way.
How do I count non-blank cells with a condition?
Two options: =COUNTIF(range, "<>") counts all non-empty cells, or =COUNTIF(range, "*") counts non-empty TEXT cells only. Use COUNTA when you just want all non-blanks with no condition.
Should I always use COUNTIFS instead of COUNTIF?
Reasonable default. COUNTIFS handles both one-condition and many-condition cases with identical first-pair syntax. But COUNTIF isn't going anywhere — it's shorter for single-condition cases and reads slightly more naturally. Either works.
What's the difference between COUNTIF and COUNT?
COUNT counts numeric cells (no condition). COUNTA counts non-blank cells (no condition). COUNTIF counts cells matching a specific condition (numeric or text). They answer different questions — see the COUNT family section above.
Which templates use COUNTIF?
Templates that track categorical status or tally rows. Prominently: Employee Attendance (count present days), Project Timeline (count tasks by status), Sales Dashboard (count sales by rep), Inventory Tracker (stock alerts), KPI Dashboard (metrics meeting threshold), Timesheet (billable vs non-billable), Invoice (line item count), and Expense Report (claims by category).
Templates that use COUNTIF
8 of our 10 templates use COUNTIF for tallying categorical data:
Skip the syntax. Ask in plain English.
The Sheets & Cells AI Add-in writes COUNTIF, COUNTIFS, and every duplicate-detection pattern — right inside Excel. Type "find duplicates in this column" and get the working formula, ready to paste.
Try the AI Add-in →