How to count unique values in Excel
To count unique values in Excel, wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(A2:A7)). UNIQUE lists each value once, and COUNTA counts the list. In older versions of Excel, use SUMPRODUCT with COUNTIF instead. This guide gives both, and shows how to count only the values that appear once.
1. Microsoft 365 and Excel 2021: COUNTA and UNIQUE
UNIQUE builds the list of different values. COUNTA counts how many cells that list has. You do not need to add anything to the sheet, because the formula works inside one cell.
Example: How many different names?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Different names | ||
| 2 | Ana | 4 | ||
| 3 | Ben | |||
| 4 | Ana | |||
| 5 | Cam | |||
| 6 | Ben | |||
| 7 | Dee |
Six names, four different ones: Ana, Ben, Cam and Dee.
2. Count values that appear only once
A unique count and a count of values that appear exactly once are different things. In the list above, Ana and Ben appear twice. Set the third argument of UNIQUE to TRUE to keep only the values that occur once.
Example: Names that appear only once
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Only once | ||
| 2 | Ana | 2 | ||
| 3 | Ben | |||
| 4 | Ana | |||
| 5 | Cam | |||
| 6 | Ben | |||
| 7 | Dee |
Only Cam and Dee occur once.
3. Older versions: SUMPRODUCT and COUNTIF
Without UNIQUE, give each cell a weight of one divided by how often its value appears, and add the weights up. A value that appears three times contributes three thirds, which is one. The formula is =SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7)). It uses SUMPRODUCT and COUNTIF.
This version returns a #DIV/0! error if the range has an empty cell. Use =SUMPRODUCT((A2:A7<>"")/COUNTIF(A2:A7,A2:A7&"")) to skip blanks.
4. Counting unique values in a pivot table
A pivot table can count distinct values if you tick "Add this data to the Data Model" when you create it, and then choose Distinct Count in the value settings. This is useful when you want the unique count per group, such as the number of different customers per region.
Common mistakes
Counting rows instead of values. COUNTA on the range counts every filled cell, repeats included.
Fix: Put UNIQUE inside COUNTA, so only the different values are counted.
Blank cells in the range. UNIQUE treats an empty cell as a value, and the older formula returns #DIV/0!.
Fix: Filter out the blanks first, or use the SUMPRODUCT version that skips them.
Spaces and spelling. "Ana" and "Ana " are counted as two names.
Fix: Clean the range with TRIM first, for example
=COUNTA(UNIQUE(TRIM(A2:A7))).
Frequently asked questions
Is UNIQUE case sensitive?
No. "ana" and "ANA" are counted as one value.
What is the difference between unique values and distinct values?
In Excel, people often use both words for the list of values with the repeats removed. Some sources use "unique" for values that occur exactly once. Check which one you mean before you choose a formula.
Can I count unique values that meet a condition?
Yes. Filter first with FILTER, then count the unique values of the result, for example =COUNTA(UNIQUE(FILTER(A2:A20,B2:B20="North"))).
Related functions
- UNIQUEUNIQUE returns each value of a list once, removing duplicates with a formula that updates by itself.
- COUNTIFCOUNTIF counts the cells in a range that meet one condition: a text, a number, a comparison like ">100" or a wildcard.
- SUMPRODUCTSUMPRODUCT multiplies ranges row by row and adds the results: total revenue, weighted averages, and sums with several conditions.
- FILTERFILTER returns the rows of a range that meet a condition, and spills them into the cells below.
More on counting unique values
The guide gives the main formulas. This piece adds how to ignore blank cells, how to count unique values per group, and how to check that your count is right.
Leave the blank cells out
UNIQUE treats an empty cell as a value, so a range with blanks gives a count that is one too high. Filter the blanks out before you count. The condition says that the cell is not equal to an empty text.
=COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>"")))FILTER returns only the rows that pass the test, and the rest of the formula works on that shorter list.
Count the unique values per group
To find how many different customers each region has, write the region in a cell, such as E2, and filter the customers by it. Fill the formula down a list of regions and you have a small summary table.
=COUNTA(UNIQUE(FILTER(A2:A100,B2:B100=E2)))If no row matches, the filter returns an error, so wrap the formula in IFERROR and show 0 instead.
Check that the count is right
- Sort the column and count the groups by eye on a small sample. It should match the formula.
- Clean the data first with TRIM. Names with extra spaces are counted as different values.
- Check what you mean by unique. A count of all different values and a count of the values that appear once give different numbers.
To remove the repeats instead of counting them, read [how to remove duplicates](/guides/remove-duplicates-in-excel).