COUNTIF in Excel: Count by One Condition | 5 Examples + Duplicate Detection | Sheets & Cells
Function · Statistical

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.

✓
Universal support since Excel 1.0
Works identically in Excel 365, 2021, 2019, 2016, 2013, 2010, 2007, 2003, Excel for the web, Google Sheets, LibreOffice, and Apple Numbers. No _xlfn. prefix. Wildcards (* and ?) work everywhere. Ship anywhere — COUNTIF just works.
✨
Good news — no argument-order flip
Unlike SUMIF vs SUMIFS (where sum_range moves from last to first), COUNTIF and COUNTIFS use the SAME argument order for the first condition. A single-condition COUNTIFS is literally identical to COUNTIF. Migration is drop-in — just add S to the function name.
COUNTIF (this page)
COUNTIF(range, criteria)
Two arguments, both required. No sum_range concept — you're just counting matches, not summing anything.
COUNTIFS (multi-condition)
COUNTIFS(criteria_range1, criteria1, ...)
First pair is IDENTICAL to COUNTIF. Add more range, criteria pairs for additional conditions (AND logic).
Quick answer
COUNTIF counts the number of cells in a range that match a single condition. Two arguments: the range to check, and the criteria to test against.
Syntax
=COUNTIF(range, criteria)
Working example
=COUNTIF(SalesRep, "Emma Thompson") → Returns 4 — Emma Thompson has 4 sales records. range = SalesRep (where to look) criteria = "Emma Thompson" (what to count)
📗 Free SUMIF + COUNTIF + AVERAGEIF workbook
9 sheets · 18-row Q1 2026 sales log · COUNTIF basics + advanced (wildcards, duplicates) · SUMIF · AVERAGEIF · legacy-vs-IFS comparison
Download .xlsx (free) Open in Sheets
Category
Statistical
Difficulty
Beginner
Excel version
All (1.0+)
Modern equiv.
COUNTIFS
1985
In Excel since v1.0
2
Arguments (both required)
100%
Cross-version support
#1
Tool for duplicate detection

Syntax breakdown

COUNTIF is the simplest of the *IF family — just two required arguments. No optional third argument like SUMIF or AVERAGEIF.

ArgumentTypeWhat 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 ignores blanks by default. Empty cells in the range are simply skipped — they don't match any criteria. If you specifically want to count blanks, use COUNTBLANK or 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?"

=COUNTIF(SalesRep, "Emma Thompson")
→ Returns 4 — four sales attributed to Emma
RepCOUNTIF formulaSale 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.

=COUNTIF(SalesProduct, "Widget*")
→ Returns 9 — total sales of Widget A + Widget B combined (5 + 4)
=COUNTIF(SalesProduct, "Gadget*")
→ Returns 6 — Gadget X + Gadget Y sales

Contains-pattern search

=COUNTIF(SalesRep, "*Kim*")
→ Returns 4 — reps whose name contains "Kim" (David Kim only in our data)
PatternInterpretationMatches
"Widget A"Exact matchOnly "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 charWidget A, Widget B
Wildcards only work in text criteria, not numeric ranges. To pattern-match numbers, convert them to text first with 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.

=COUNTIF(SalesAmount, ">10000")
→ Returns 3 — three sales exceeded $10,000
=COUNTIF(SalesAmount, "<3000")
→ Returns 5 — five sales were below $3,000

"Between X and Y" — need COUNTIFS

=COUNTIFS(SalesAmount, ">=3000", SalesAmount, "<=6000")
→ Returns 5 — sales between $3,000 and $6,000 inclusive

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

=COUNTIF(SalesAmount, ">="&B2)
→ Sales at or above the value in B2. The & concatenates the operator with the cell value.
The & 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?

=COUNTIF(SalesRep, B2) > 1
→ Returns TRUE or FALSE. Emma Thompson appears 4 times → TRUE. Aisha Patel appears 2 times → TRUE. Some hypothetical rep appearing once → FALSE.

Readable duplicate flag

=IF(COUNTIF(SalesRep, B2) > 1, "DUPLICATE", "unique")
→ Shows "DUPLICATE" or "unique" for each row. Cleaner than TRUE/FALSE for reports.
Cell (B2)COUNTIF(SalesRep, B2)> 1?Flag
Emma Thompson4TRUEDUPLICATE
David Kim4TRUEDUPLICATE
Sofia Rodriguez3TRUEDUPLICATE
Aisha Patel2TRUEDUPLICATE
Solo Person1FALSEunique
Missing Name0FALSEunique

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:

=IF(COUNTIF($B$2:B2, B2) > 1, "duplicate", "")
→ Uses an expanding range (anchored start, relative end). Row 2 checks only B2:B2 → count is 1 → blank. Row 5 checks B2:B5 → count is 2 → "duplicate". First occurrence stays unmarked; every repeat gets flagged.

Conditional formatting to highlight duplicates

Same idea, wrapped in a conditional format rule instead of a formula:

  1. Select the range (e.g., B2:B100)
  2. Home → Conditional Formatting → New Rule → Use a formula
  3. Formula: =COUNTIF($B$2:$B$100, B2)>1
  4. Pick a fill color → OK
This is why COUNTIF is on every "essential Excel" list. No other function makes duplicate detection this compact. Even in Excel 365 with 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.

=SUMPRODUCT(1 / COUNTIF(SalesRep, SalesRep))
→ Returns 6 — the number of DISTINCT reps in our 18-row dataset

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.

RepOccurrences1 / count contribution
Emma Thompson (4 rows)44 × (1/4) = 1
David Kim (4 rows)44 × (1/4) = 1
Sofia Rodriguez (3 rows)33 × (1/3) = 1
Michael Chen (3 rows)33 × (1/3) = 1
Aisha Patel (2 rows)22 × (1/2) = 1
James Wilson (2 rows)22 × (1/2) = 1
SUMPRODUCT total →6

Modern alternative — Excel 365

=COUNTA(UNIQUE(SalesRep))
→ Same result — 6 unique reps. Much cleaner. But UNIQUE is Excel 365 only — the SUMPRODUCT trick above works everywhere.
The SUMPRODUCT trick is legendary. It appears in Excel forums, MVP blogs, and pre-2019 books as "the way" to count distinct values. Learn it once and you have a portable pattern that works in Excel 2003, Google Sheets, LibreOffice — everywhere. Even if you're on 365, understanding it makes you a stronger Excel user.

Interactive playground

Try it Live COUNTIF demonstration

Mirrors live cells from the workbook. Edit the yellow input → the blue answer updates.

Input · Rep name
Emma Thompson
Output · Count
4
=COUNTIF(SalesRep, B2)
Input · Rep name
Emma Thompson
Output · Duplicate check
DUPLICATE
=IF(COUNTIF(SalesRep, B2)>1, "DUPLICATE", "unique")

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.

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.

QuestionLegacy — COUNTIFModern — 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

ResultWhy it happensBroken → 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()
📗 Every example above, in one workbook
Exact match · wildcards · comparison operators · duplicate detection · unique count · SUMPRODUCT trick · legacy-vs-IFS comparison.
Download legacy-ifs-examples-2026.xlsx

Related functions

Complementary functions

Excel version compatibility

COUNTIF has been in Excel since version 1.0 (1985). Every platform supports it identically:

PlatformSupports COUNTIF?Notes
Excel 365 (Windows & Mac)✓ YesFull support
Excel 2021 / 2019 / 2016 / 2013 / 2010 / 2007 / 2003✓ YesFull support
Excel for the web✓ YesFull support
Excel on iPad & iPhone✓ YesFull support
Google Sheets✓ YesSame syntax
LibreOffice Calc✓ YesFull support
Apple Numbers✓ YesFull support

How to write a COUNTIF from scratch

  1. Identify the range you want to check

    Which column or range holds the values you're testing? That's the first argument.

  2. Write the criteria

    Exact text: "Emma Thompson". Number comparison: ">10000". Wildcard: "Widget*". Cell reference: B2. Cell reference with operator: ">="&B2.

  3. For duplicate detection, compare the result to 1

    =COUNTIF(range, cell) > 1 returns TRUE for duplicates. Wrap in IF for a readable label.

  4. For unique counts, use SUMPRODUCT + COUNTIF

    =SUMPRODUCT(1/COUNTIF(range, range)). Each unique value contributes exactly 1 to the sum.

  5. Need 2+ conditions → COUNTIFS

    Same argument order as COUNTIF for the first pair. Just add more range, criteria pairs for AND logic.

Functions used with COUNTIF

🎁 Grab the free SUMIF + COUNTIF + AVERAGEIF workbook
9 sheets covering everything on this page — basics, wildcards, duplicates, unique counts, plus the legacy-vs-IFS comparison.
Download legacy-ifs-examples-2026.xlsx

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 →