Description
Use this function to extract a list of unique values in a list or range. Values can be text, numbers, dates, times, etc.
Syntax
UNIQUE(array, [by_col], [exactly_once])
Inputs
This function accepts the following argument:
| Name | Required | Description | Valid Input |
|---|---|---|---|
array |
Yes | The range or array to be examined. | A cell, a cell range, or a formula which results in either of these. |
by_col |
No | How to compare and extract. By row = FALSE (default); by column = TRUE. |
A cell reference, a logical value (TRUE or FALSE), a number (0 is FALSE, other numbers are TRUE), or a formula which results in any of these. |
exactly_once |
No | FALSE (0)= all unique values (default). TRUE (1) = values that occur once. | A cell reference, a logical value (TRUE or FALSE), a number (0 is FALSE, other numbers are TRUE), or a formula which results in any of these. |
Examples
Sample data
Data set 1
| A | B | C | |
|---|---|---|---|
| 1 | Grade | Name | Score |
| 2 | A | George | 100 |
| 3 | B | Lina | 100 |
| 4 | B | Ezsther | 98 |
| 5 | A | George | 95 |
| 6 | B | Maria | 95 |
| 7 | F | Garth | 92 |
| 8 | B | LeShawn | 91 |
Data set 2
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Tigers | Eagles | Cougars | Bears | Ravens | Thunderbirds |
| 2 | Score | 8 | 5 | 7 | 7 | 5 | 6 |
Data set 3
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Q1 Data | |||
| 2 | Name | Sales | Region | Commission |
| 3 | John | 45000 | North | 4500 |
| 4 | Alice | 52000 | South | 5200 |
| 5 | Q2 Data | |||
| 6 | Name | Sales | Region | Commission |
| 7 | John | 48000 | North | 4800 |
| 8 | Alice | 55000 | South | 5500 |
Data set 4
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Amount | Date | Price | Total |
| 2 | Apples | 12 | 1/8/2023 | $0.99 | $11.88 |
| 3 | Apples | 3 | 10/9/2022 | $1.36 | $4.08 |
| 4 | Apples | 1 | 11/17/2022 | $1.55 | $1.55 |
| 5 | Apples | 4 | 1/1/2023 | $1.44 | $5.76 |
| 6 | Apples | 10 | 11/16/2022 | $1.42 | $14.20 |
| 7 | Pears | 32 | 11/28/2022 | $1.41 | $45.12 |
| 8 | Pears | 27 | 11/24/2022 | $1.47 | $39.69 |
| 9 | Pears | 15 | 11/13/2022 | $1.50 | $22.50 |
| 10 | Peaches | 14 | 11/13/2022 | $1.02 | $14.28 |
| 11 | Peaches | 12 | 10/1/2022 | $1.58 | $18.96 |
| 12 | Peaches | 10 | 12/7/2022 | $1.17 | $11.70 |
| 13 | Peaches | 17 | 10/9/2022 | $1.02 | $17.34 |
| 14 | Kiwis | 31 | 11/23/2022 | $0.97 | $30.07 |
| 15 | Kiwis | 28 | 11/4/2022 | $1.49 | $41.72 |
| 16 | Kiwis | 32 | 10/10/2022 | $0.93 | $29.76 |
Sample formulas
| Use case | Formula | Result | ||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Return every value in a range that occurs at least once. |
=UNIQUE(A1:A8)
|
This formula returns every value in the range A2:A8 of Table 1
that occurs at least once. For data set 1,
this formula would return the following:
|
||||||||||||||||||||||||||||||||||||
| Return every value in a range that occurs only once. |
=UNIQUE(A2:A8,0,1)
|
This formula returns every value in the range A2:A8 of Tabl 1
that occurs only once. For data set 1,
this formula returns the following:
|
||||||||||||||||||||||||||||||||||||
| Return every value in a range that occurs only once. |
=UNIQUE(B2:G2,TRUE,1)
|
This formula returns every value in the range B2:G2 of Table 1
that occurs only once. For data set 2,
this formula returns the following:
|
||||||||||||||||||||||||||||||||||||
| Extract unique values from multiple columns |
=UNIQUE(B2:B8&": "&A2:A8)
|
This formula returns unique values from multiple columns.
For data set 1, this formula would return
the following: (Only 1 instance of "George" that got an "A").
|
||||||||||||||||||||||||||||||||||||
| Extract an nth-largest value. |
=LARGE(UNIQUE(C1:C12),3)
|
This formula returns the third-largest score. UNIQUE creates
an array of the scores, and
LARGE
then selects the 3rd-largest value in that array. For data set 1,
this formula would return the following:
|
||||||||||||||||||||||||||||||||||||
| Combine separate data sets and sum the values for rows that have identical column 1 entries. |
This use case requires 2 formulas in adjacent cells,
one for the names, and one for the totals.
|
Assuming that you want the results in columns E and F, the solution for data set 3 would be as follows: Formula 1 (in cell E1) returns the UNIQUE names from column A and uses VSTACK to place them in vertically adjacent cells. Formula 2 (in cell F1) returns the values associated with the unique names. The two SUMIF statements then add those values together, and VSTACK places them in vertically adjacent cells so that they match the names. The displayed results are as follows:
|
||||||||||||||||||||||||||||||||||||
| Extract unique records based on the values in column A while returning the corresponding data from columns A, B, and E. |
This use case requires 3 formulas in adjacent cells, one for the item names, one for the count totals and one for the value totals.
|
Assuming that you want the results in columns F, G, and
H, the solution for data set 4 would be as follows: Formula 1 (in cell F2) produces the list of unique item names; this returns the UNIQUE names from column A in vertically adjacent cells:
Formula 2 (in cell G2) uses SUMIFS to produce the total counts associated with each item:
Formula 3 (in cell H2) uses
SUMIFS
to produce the total values associated with each item,
and then uses
|
Notes
- As with all array functions, UNIQUE dynamically populates results across adjacent cells, starting from the cell containing the UNIQUE function.
- Responses are returned in individual cells.
-
By default, UNIQUE extracts values as rows. If
by_colis inactive (>blank</0/FALSE), then the responses are in a row; ifby_colis active (1/TRUE), then the responses are in a column. - UNIQUE is **not** case-sensitive. UNIQUE regards "WORKIVA", "Workiva", and "workiva" as exactly the same word.
- UNIQUE automatically sums numeric values when it finds duplicate entries in the first column.
Tips
- If you have header row(s) and don't want the headers to be included, start your array values one row down (for example, in the above data sets, start with A2 rather than A1).
-
If you have data in multiple columns and want unique **rows**:
=UNIQUE(A1:C10)will return unique **row combinations** rather than just distinct values from a single column. -
If you want to find unique **columns** instead of rows, use the following
formula:
=UNIQUE(A1:C10, , TRUE). This checks for uniqueness column-wise instead of row-wise. -
To extract unique values that appear just once in the source data,
set
exactly_onceto TRUE or 1. -
To return values that appear only once in the range A1:A10, use the
following formula instead:
=FILTER(A1:A10, COUNTIF(A1:A10, A1:A10) = 1). Unlike UNIQUE, which keeps the first occurrence of duplicates, this formula removes all duplicates entirely. -
To count the **number** of unique values, without displaying the
unique list, use the following formula, use
ROWS:
=ROWS(UNIQUE(A1:A10)). -
If UNIQUE returns an empty result (for example, due to filters),
wrap it in
IFERROR:
=IFERROR (UNIQUE(A1:A10), "No unique values found").
Array formula functions
Here are the other array formula functions supported in Workiva spreadsheets.