The UNIQUE function
Returns the distinct values of a range as a list that spills into the cells below.
Syntax
UNIQUE(array, [by_col], [exactly_once])- array
- The range to take values from.
- by_col (optional)
- TRUE to compare columns instead of rows.
- exactly_once (optional)
- TRUE to return only values that appear once.
Examples
E1=UNIQUE(A2:A6)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | North | |
| 2 | North | Shoes | 500 | South | |
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |
E1=COUNTA(UNIQUE(B2:B6))
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 2 | |
| 2 | North | Shoes | 500 | ||
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |