Home › Excel basics › Count unique values

How to Count Unique Values in Excel

To count unique values in Excel 365, use =COUNTA(UNIQUE(A2:A8)). In older versions, =SUMPRODUCT(1/COUNTIF(A2:A8, A2:A8)) does the same job. Below you'll find both, plus how to count with a condition and the difference between "distinct" and "unique".

Excel CSE · 3 min read

Seven orders, but how many different customers? Anna, Ben, Chris and Dana: 4.

Distinct or unique: which do you need?

Most people mean the first when they say "count unique values". Both are covered below.

Excel 365: COUNTA and UNIQUE

UNIQUE returns the list of different names and COUNTA counts them: 4. For names that appear exactly once, set UNIQUE's third argument to TRUE:

Result: 2. If the range can contain empty cells, filter them out first, otherwise the blank counts as a value:

Older versions: SUMPRODUCT and COUNTIF

How it works: COUNTIF counts how often each name occurs. Anna gets 3 on each of her three rows, so each of those rows adds 1/3, together exactly 1. Every name adds up to 1, so the total is the number of different names: 4.

Empty cells cause #DIV/0!. This version skips them:

To count names that appear only once:

Count unique values with a condition

How many different customers ordered in South? In Excel 365:

South has Ben, Anna and Ben, so the answer is 2. If no row matches, FILTER returns #CALC!; wrap it as IFERROR(…, 0).

With a pivot table (no formula)

1. Select the data and choose Insert › PivotTable.
2. Tick Add this data to the Data Model and click OK.
3. Drag Region to Rows and Customer to Values.
4. Click the Customer field in Values, choose Value Field Settings and pick Distinct Count.

You now see the number of different customers per region. Without the Data Model tick, Distinct Count doesn't appear in the list. More in the pivot table guide.

List the unique values instead

Want the names, not the count? =UNIQUE(A2:A8) or =SORT(UNIQUE(A2:A8)) gives the list. In older versions, copy the column and use Remove Duplicates.