How to Count Unique Values in Excel

To count unique values in Excel 2021 or Microsoft 365, wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(B2:B11)). UNIQUE lists each different value once, and COUNTA counts how many there are. This guide shows that formula, how to add a condition, the two traps that make the count wrong, and a formula for older versions of Excel. "How many different customers do we have?" is a common interview question, so it is worth knowing well.

How to count unique values with COUNTA and UNIQUE#

=COUNTA(UNIQUE(range)) is the quickest way to count distinct values. Here is an order list where some customers ordered more than once:

There are 10 orders. To count the different customers:

=COUNTA(UNIQUE(B2:B11))

UNIQUE(B2:B11) returns Brightline, Northwind, Kestrel, Orchid Co, Juniper and Maple Ltd. COUNTA counts the six names, so the result is 6. (COUNT would return 0, because it only counts numbers.)

UNIQUE needs Excel 2021, Excel for Microsoft 365 or Excel for the web. If you type =UNIQUE(B2:B11) on its own, the list spills into the cells below; see the UNIQUE function guide for how spilling works, and how to fix a #SPILL! error if something is in the way. Inside COUNTA nothing spills, because the result is one number.

How to count values that appear exactly once#

UNIQUE has an optional third argument, exactly_once. Set it to TRUE to keep only values that appear one time.

=COUNTA(UNIQUE(B2:B11,FALSE,TRUE))

The result is 3: Orchid Co, Juniper and Maple Ltd each ordered once. The second argument, FALSE, means "compare rows, not columns", which is right for a list that runs down a column.

These are two different questions. "How many different customers?" is 6. "How many one-time customers?" is 3. Read the task carefully before you choose.

How to count unique values with a condition#

To count distinct values that meet a condition, filter the rows first and then count what's unique. How many different customers are in the East?

=COUNTA(UNIQUE(FILTER(B2:B11,C2:C11="East")))

FILTER(B2:B11,C2:C11="East") returns the five East orders: Brightline, Brightline, Kestrel, Brightline, Kestrel. UNIQUE reduces that to Brightline and Kestrel, and COUNTA gives 2. Change "East" to "West" and you get 3. The FILTER function guide covers more ways to write the condition, such as (C2:C11="East")*(A2:A11<>"") for two conditions.

Watch out: an empty FILTER still counts as 1#

If no rows match, FILTER returns a #CALC! error, and COUNTA counts that error as one item. So =COUNTA(UNIQUE(FILTER(B2:B11,C2:C11="North"))) returns 1, even though there are no North orders. Guard against it by checking first:

=IF(COUNTIF(C2:C11,"North")=0,0,COUNTA(UNIQUE(FILTER(B2:B11,C2:C11="North"))))

This returns 0. Our COUNTIF guide explains the COUNTIF check, and the COUNTIF function reference lists its criteria rules.

Count unique values for every group#

To build a small summary with one row per region, list the regions with =UNIQUE(C2:C11) in E2. It spills East, West and South. Then in F2, count the customers for each one and fill down:

=COUNTA(UNIQUE(FILTER($B$2:$B$11,$C$2:$C$11=E2)))

The results are 2 for East, 3 for West and 1 for South. Lock the data ranges with dollar signs and leave E2 relative so each row checks its own region. A pivot table can give the same summary; see the Distinct Count option below.

Why is my unique count one too high? (Blank cells)#

A blank cell in the range adds one extra item to UNIQUE's list, which Excel shows as 0. COUNTA counts it, so the result is one higher than the number of real values. Remove blanks before counting:

=COUNTA(UNIQUE(FILTER(B2:B11,B2:B11<>"")))

The test B2:B11<>"" keeps only cells that aren't empty. Cells that hold just a space are not empty, so clean those first with TRIM, or use count non-blank cells with a condition to find them.

Also note that UNIQUE is not case-sensitive (Kestrel and KESTREL count once), but it does treat "Kestrel" and "Kestrel " (with a trailing space) as different values.

How to count unique values in older Excel (2019 and earlier)#

Without UNIQUE, use SUMPRODUCT and COUNTIF. This formula works in every version:

=SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11))

How it works: COUNTIF(B2:B11,B2:B11) counts how often each row's value appears. Brightline appears 3 times, so each of its rows gets 1/3; Kestrel gets 1/2 twice; Juniper gets 1/1. Each customer's fractions add up to exactly 1, so the total is 6.

This version returns #DIV/0! if the range contains a blank cell. The fix adds &"" and skips blanks:

=SUMPRODUCT((B2:B11<>"")/COUNTIF(B2:B11,B2:B11&""))

On large ranges (tens of thousands of rows) the COUNTIF method can be slow, because it counts the whole range once for every row.

Count unique values without a formula#

Two point-and-click options are worth knowing:

• Remove Duplicates: copy the column to a new sheet, select it, and choose Data > Remove Duplicates, then count the rows that remain. It changes the data, so always work on a copy. See UNIQUE vs Remove Duplicates for when each fits.
• Pivot table Distinct Count (Excel for Windows): when you create the pivot table, tick Add this data to the Data Model. Then in Value Field Settings, choose Distinct Count. Without the Data Model, the option doesn't appear.

For a total by group rather than a count, such as revenue for "North or East", see SUMIFS with OR logic.

Practise it#

Try the live exercise on this page to count the different customers. Then practise UNIQUE in a live spreadsheet, including the exactly-once option, and use the COUNTIF lesson to get comfortable with the criteria that power the older formula.