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.
The Data
| 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. Add an IF to handle blanks: =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:
Excel 365
=COUNTA(UNIQUE(FILTER(A2:A100, B2:B100="East")))
Filters to East region first, then counts unique values.
All versions (array formula)
=SUMPRODUCT((B2:B100="East")/COUNTIFS(A2:A100, A2:A100, B2:B100, "East"))
Counts unique customers where region is “East”. Enter with Ctrl+Shift+Enter in older Excel.
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.
Start a Project