To count unique values in an Excel column, use =COUNTA(UNIQUE(A2:A8)) on Microsoft 365. On any older version use =SUMPRODUCT(1/COUNTIF(A2:A8, A2:A8)), which gives each row 1 divided by the number of times its value appears, so every group of duplicates adds up to exactly 1. A Pivot Table with Distinct Count is a third option that needs no formula.
COUNTA tells you how many cells have data. But how many unique customers, products, or regions are in a column, ignoring duplicates? Excel doesn’t have a built-in COUNTUNIQUE function, but there are several ways to get the answer depending on your Excel version.
Sample customer list with duplicates
| A | |
|---|---|
| 1 | Customer |
| 2 | Acme Corp |
| 3 | Bright LLC |
| 4 | Acme Corp |
| 5 | Chen Inc |
| 6 | Bright LLC |
| 7 | Acme Corp |
| 8 | Delta Group |
COUNTA returns 7 (total entries). The unique count should be 4 (Acme, Bright, Chen, Delta).
Method 1: UNIQUE + COUNTA (Excel 365)
The cleanest approach if you’re on Microsoft 365:
=COUNTA(UNIQUE(A2:A8))
UNIQUE extracts the distinct values, COUNTA counts them. Result: 4. Simple, readable, and fast.
Bonus: List the unique values
Just =UNIQUE(A2:A8) on its own spills a list of unique values into adjacent cells. Great for building summary tables or feeding dropdown lists.
Method 2: SUMPRODUCT (All Excel Versions)
The classic approach that works everywhere:
=SUMPRODUCT(1/COUNTIF(A2:A8, A2:A8))
COUNTIF counts how many times each value appears. 1/count gives a fraction (1/3 for Acme which appears 3 times). SUMPRODUCT sums these fractions: each unique value’s fractions add up to exactly 1. Result: 4.
Blank cells will crash this
If any cells in the range are blank, COUNTIF returns 0, and 1/0 causes a #DIV/0! error. Handle blanks with this version: =SUMPRODUCT((A2:A8<>"")/COUNTIF(A2:A8, A2:A8&""))
Method 3: Pivot Table Count
For a quick visual count without formulas:
1Select your data and go to Insert → PivotTable
2Drag the column to both Rows and Values
3The Values field shows “Count of Customer”. The number of rows in the Pivot Table is your unique count.
In Excel 2013+, you can also change the Values field to “Distinct Count” if you enable the Data Model (check “Add this data to the Data Model” when creating the Pivot Table).
Conditional Unique Count
Count unique customers in the “East” region only, with each row’s region in column B next to the customer name:
Excel 365
=IFERROR(ROWS(UNIQUE(FILTER(A2:A100, B2:B100="East"))), 0)
Filters to East region first, then counts unique values. When no row is in East, FILTER returns an error, and IFERROR turns it into 0. ROWS is used instead of COUNTA because COUNTA counts that error as 1.
All versions
=SUMPRODUCT((B2:B100="East")/(COUNTIFS(A2:A100, A2:A100, B2:B100, "East")+(B2:B100<>"East")))
Counts unique customers where region is “East”. The added (B2:B100<>"East") adds 1 to the divisor of every row outside East, so those rows never divide by 0. Their numerator is 0, so they add nothing instead of returning a #DIV/0! error. SUMPRODUCT works through the arrays itself, so no Ctrl+Shift+Enter is needed.
Common questions
Does it count blank cells as a unique value?
UNIQUE treats blanks as one entry, so a column with gaps counts one too many. Exclude them with =IFERROR(ROWS(UNIQUE(FILTER(A2:A100, A2:A100<>""))), 0), which also returns 0 for a completely empty column.
Is the count case sensitive?
No. “Acme Corp” and “ACME CORP” count as one value, because UNIQUE and COUNTIF both ignore case. To count them as two, the comparison has to use EXACT, which does match case.
Which method works in older Excel?
The SUMPRODUCT version works in every version back to Excel 2007. UNIQUE requires Microsoft 365 or Excel 2021.
When Formulas Aren’t Enough
Counting unique values is often just the first step in a larger analysis:
- Unique counts across multiple columns: unique combinations of customer + product + region
- Unique counts with fuzzy matching: “Acme Corp” and “Acme Corporation” should count as one
- Trending unique counts: how many new unique customers each month vs returning
- Cross-file unique counts: counting distinct values across multiple workbooks or data sources
- Dashboard integration: feeding unique counts into KPI cards, charts, and summary panels
A custom VBA analytics tool handles complex unique counting, fuzzy deduplication, trend analysis, and feeds results directly into formatted dashboards.
“Quick turnaround. Right first time.”
Gareth H. · Broadstairs · PeoplePerHour
Need Automated Data Analysis and Reporting?
Tell us about your analysis needs and we’ll build a tool that does the heavy lifting.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.