Excel track · Exercise 79 of 86 Paid
UNIQUE: one row per distinct value
Count the distinct couriers in a column without retyping the list.
Advanced
Courier delivery log
Read-only data · answer in B8
In B8, count how many different couriers appear in A2:A7 by collapsing the list with UNIQUE.
| A | B | |
|---|---|---|
| 1 | Courier | Deliveries |
| 2 | Swift | 4 |
| 3 | Nordic | 7 |
| 4 | Swift | 2 |
| 5 | Crane | 5 |
| 6 | Nordic | 3 |
| 7 | Swift | 6 |
| 8 | Distinct couriers |
Reference answer
What it does
UNIQUE reads a range and returns one copy of each distinct value, in the order it first met them. In A2:A7, Swift appears three times, Nordic twice and Crane once, so UNIQUE(A2:A7) gives Swift, Nordic, Crane.
The exercise wants a count, so the block is wrapped: =COUNTA(UNIQUE(A2:A7)) counts the rows it occupies and returns 3 in B8. The third argument, exactly_once, changes the meaning. Set it to TRUE and only values that appear a single time come back, which here leaves Crane alone.
A block needs empty cells to land in. If one of them is already occupied, the formula returns #SPILL! and names the cell in the way. Excel leaves the neighbouring data where it is. UNIQUE alone needs three rows here, so leave a few blanks under anything that returns a block.
Syntax
=UNIQUE(array, [by_col], [exactly_once])Worked example
On Courier delivery log, =COUNTA(UNIQUE(A2:A7)) returns 3.
=COUNTA(UNIQUE(A2:A7))Common errors
| Error | What it means and how to fix it |
|---|---|
#SPILL! | The result needs three rows and one of them holds something else, so Excel stops and names the cell in the way. |
#N/A | exactly_once returned nothing because no courier appears a single time, leaving the block empty. |
#NAME? | The workbook's version of Excel predates dynamic arrays, so UNIQUE does not exist there. |
At work
A courier list that grows every week, and the dropdown fed from it without retyping the names.