UNIQUE

About this lesson

UNIQUE returns the distinct values in a range as an array. COUNTA around it counts how many different values there are.

By the end you can

list the distinct values in a column with UNIQUE.
count how many different values there are with COUNTA around UNIQUE.

The idea

The question "how many different customers" had no clean answer in Excel for thirty years. UNIQUE is that answer: the distinct values, in first-seen order, as an array. In Excel 365 the array spills down the sheet on its own. Wrapped in COUNTA it is a single number, and that is the form most sheets use it in.

This grid does not spill. A cell holding an array shows its first value. That is what Excel did before 2019, and what older versions still do. So every step here asks for the first result, or for a function wrapped round the whole array. The third argument, TRUE, changes the question from "each value once" to "only values that occur once".

The mistake to watch for

Counting a list that contains an empty cell. UNIQUE returns one distinct blank for it, and COUNTA counts the blank as a value. ROWS gives the true count. And the third argument changes the question, from each value once to only the values that occur once. That is a different number that looks like a smaller version of the same one.

Where this comes up again

COUNT and COUNTA Spreadsheet Basics
Putting It Together Conditional Math
SUBTOTAL: One Function, Nine Aggregates Summaries and Reports

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In E2, list the distinct customers with UNIQUE over the customer column. Excel would spill the whole list down the column from here. This grid does not spill, so it shows the first result only. The next steps wrap the array in a function that reads all of it.

To begin, type it exactly:

=UNIQUE(B2:B9)

Row	A	B	C	E
1	Ticket	Customer	Product	First distinct
2	101	Acme	Tee
3	102	Birch	Hoodie	Distinct customers
4	103	Acme	Cap
5	104	Cedar	Tee	Only once
6	105	Birch	Tee
7	106	Dale	Hoodie	Distinct products
8	107	Acme	Tee
9	108	Elm	Cap

Every step

1

In E2, list the distinct customers with UNIQUE over the customer column. Excel would spill the whole list down the column from here. This grid does not spill, so it shows the first result only. The next steps wrap the array in a function that reads all of it. To begin, type it exactly: =UNIQUE(B2:B9).

Hint. One range.
2

In E4, count how many distinct customers there are: COUNTA around the UNIQUE.

Hint. COUNTA of the UNIQUE.
3

ROWS counts the rows of anything. Read =ROWS(UNIQUE(B2:B9)) and say what it returns.

Hint. Same list, counted by rows.
4

UNIQUE's third argument, TRUE, asks for values that appear exactly once. In E6, find the first customer with only one ticket.

Hint. Skip the second argument, TRUE for the third.
5

In E8, write a formula using COUNTA and UNIQUE that gives the number of distinct products.

Hint. COUNTA of UNIQUE over column C.