Get a list of unique values in Excel, and count them
UNIQUE returns each value once and updates by itself. How to list distinct values, count them, sort them, and what to use in older versions of Excel.
To get a list of unique values in Excel, type =UNIQUE(B2:B11).
It lists each value once. To count the unique values, put COUNTA around it:
=COUNTA(UNIQUE(B2:B11)).
UNIQUE needs Excel 2021, Excel 2024, Microsoft 365 or Excel for the web. For older versions, see the last section.
Here is a training log. The company ran 3 sessions, and each row is one person at one session. The manager asks a simple question: how many people came? The answer is not 10.
| Row | A | B | C |
|---|---|---|---|
| 1 | Session | Name | Team |
| 2 | 1 | Omar Haddad | Sales |
| 3 | 1 | Keiko Sato | Finance |
| 4 | 1 | Luis Romero | Sales |
| 5 | 2 | Omar Haddad | Sales |
| 6 | 2 | Nadia Petrova | IT |
| 7 | 2 | Keiko Sato | Finance |
| 8 | 3 | Grace Mensah | Finance |
| 9 | 3 | Omar Haddad | Sales |
| 10 | 3 | Luis Romero | Sales |
| 11 | 3 | Pieter Jansen | IT |
Count the different people
=COUNTA(UNIQUE(B2:B11)) returns 6.
There are 10 rows, but only 6 different names. Omar Haddad came 3 times and counts once.
UNIQUE makes the list of names. COUNTA counts the items in that list. The answer is one number
in one cell. =ROWS(UNIQUE(B2:B11)) also returns 6.
List them
Type =UNIQUE(B2:B11) in E2. It fills E2 to E7:
| Omar Haddad |
| Keiko Sato |
| Luis Romero |
| Nadia Petrova |
| Grace Mensah |
| Pieter Jansen |
The names come in the order they first appear in the log. One formula fills 6 cells. This is called spilling.
Keep the cells below E2 empty. If something is in the way, you get
#SPILL! instead of the list. Add a person to the log, and the
list grows by itself.
UNIQUE does not care about capitals. Omar Haddad and omar haddad are one name to it. It keeps the one it meets first.
Sort the list
=SORT(UNIQUE(B2:B11)) fills E2 to E7 from A to Z:
| Grace Mensah |
| Keiko Sato |
| Luis Romero |
| Nadia Petrova |
| Omar Haddad |
| Pieter Jansen |
For Z to A, use =SORT(UNIQUE(B2:B11),1,-1). The 1 is the
column, and -1 means the other way.
Only the people who came once
UNIQUE has a third argument. Set it to TRUE and the question changes. You no longer get each name once. You get the names that appear only once.
=UNIQUE(B2:B11,,TRUE) fills E2 to E4:
| Nadia Petrova |
| Grace Mensah |
| Pieter Jansen |
Note the 2 commas. The second argument is for data that runs across the sheet. You leave it empty here.
Be careful with the words in a report. =COUNTA(UNIQUE(B2:B11,,TRUE))
returns 3. That is the people who came once, not the number of people. Both look like "how many
people", and they are different numbers.
How many times each person came
Put a COUNTIF next to the UNIQUE list. In F2, type
=COUNTIF($B$2:$B$11,E2) and fill it down to F7.
| Omar Haddad | 3 |
| Keiko Sato | 2 |
| Luis Romero | 2 |
| Nadia Petrova | 1 |
| Grace Mensah | 1 |
| Pieter Jansen | 1 |
The counts add up to 10, the number of rows in the log. That is the quick test that nothing is missing.
The dollar signs keep the log range fixed as you fill down. The COUNTIF practice article has more on this kind of count.
Two things that make the count wrong
A range longer than the data. People often type B2:B100 to leave room for new rows. The empty cells come into UNIQUE as one extra value, and it shows as 0.
=UNIQUE(B2:B20) lists the 6 names and then a 0. COUNTA counts
that 0, so =COUNTA(UNIQUE(B2:B20)) returns 7, not 6.
Fix: filter out the empty cells first.
=COUNTA(UNIQUE(FILTER(B2:B20,B2:B20<>""))) returns 6.
The FILTER article explains the test inside it.
A space you cannot see. "Omar Haddad" with a space at the end is a different value to UNIQUE. It shows up as a second Omar in the list, and the count is one too high. Clean the names first with TRIM.
In Excel 2019 and older
There is no UNIQUE. You have 2 choices.
Remove Duplicates. Copy the Name column to a new sheet first. Select the copy, then go to Data, then Remove Duplicates. Excel deletes the repeated rows and keeps the first of each.
Always work on a copy. Remove Duplicates changes the cells themselves. It also does not update, so you must do it again when the log grows.
A formula for the count.
=SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11)) returns 6 in any version.
Each row gets 1 divided by how often its name appears. Omar's 3 rows each give 1/3, so together they add up to 1. Every person adds up to 1, so the total is the number of people.
It fails on an empty cell in the range, with #DIV/0!. So give it
the exact range, with no empty rows.
Questions people ask
How do I count unique values with a condition?
Filter first, then count. =COUNTA(UNIQUE(FILTER(B2:B11,C2:C11="Finance")))
returns 2. Keiko Sato and Grace Mensah are the 2 people from Finance.
Can UNIQUE work on 2 columns?
Yes. =UNIQUE(B2:C11) returns each name and team pair once. Here
that is 6 rows, because each person is in one team.
Is a pivot table better for this?
For a one-off report, a pivot table is fine. UNIQUE is better when the list must update by itself, or feed another formula.
The lessons below practise UNIQUE, SORT and a count for each value. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.