How to count unique values in Excel
Excel formula
=COUNTA(UNIQUE(A2:A10))In Excel 365, wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(range)). In older versions, use =SUMPRODUCT(1/COUNTIF(range,range)).
- Result on the example
- 5 in
E1 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
UNIQUE returns each customer once, as a list, and COUNTA counts that list. Nothing spills on the sheet because COUNTA turns the list into one number.
Google Sheets has a function for exactly this, COUNTUNIQUE, and =COUNTA(UNIQUE(A2:A10)) works there too.
=COUNTA(UNIQUE(A2:A10))Result5
A2:A109 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …A range: 9 cells in one column.
UNIQUE(A2:A10){5 rows × 1 col} "Northwind", "Contoso", "Fabrikam", "Litware", …UNIQUE: Distinct rows of a range. Spills.- array
A2:A109 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …
- array
COUNTA(UNIQUE(A2:A10))5COUNTA: Counts cells that are not empty.- value1
UNIQUE(A2:A10){5 rows × 1 col} "Northwind", "Contoso", "Fabrikam", "Litware", …
- value1
In Google Sheets
Google Sheets needs a different formula: =COUNTUNIQUE(A2:A10). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10)) | 5 | Works in every Excel version. Each name adds 1/n for each of its n rows, so every name adds up to 1. It returns #DIV/0! if the range has blank cells. |
| =ROWS(UNIQUE(A2:A10)) | 5 | The same count, using the number of rows UNIQUE returns. |
Common mistakes
- Blank cells inside the range count as one extra unique value in
UNIQUE. Use=COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>"")))for a column with gaps. - UNIQUE is not case-sensitive: "contoso" and "Contoso" are one value.
- UNIQUE needs Excel 2021 or Microsoft 365. In older versions you get #NAME?.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count unique values
E1, count how many different customers placed orders in A2:A10.Questions
How do I count unique values with a condition?
Filter first, then count: =COUNTA(UNIQUE(FILTER(A2:A10,B2:B10>500))) counts customers with at least one order over 500.
What is the difference between unique and distinct?
In Excel, UNIQUE(range) returns distinct values (each once). UNIQUE(range,,TRUE) returns only values that appear exactly once.
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.