UNIQUE

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 Explanation and 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:

Grade
A
B
C
F

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").

George: A
Lina: B
Ezsther: B
Maria: B
Garth: F
LeShawn: B

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.

=UNIQUE(VSTACK(A2:D4, A6:D8))
=SUMIF(A3:A4,UNIQUE(VSTACK(A3:A4,A7:A8)),B3:B4) +SUMIF(A7:A8,UNIQUE(VSTACK(A3:A4,A7:A8)),B7:B8)

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:

E	F
John	93000
Alice	107000

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.

=UNIQUE(A2:A16)
=SUMIFS(B2:B16,A2:A16,UNIQUE(A2:A16))
=TEXT(SUMIFS(E2:E16,A2:A16,UNIQUE(A2:A16)),"$#,##0.00")

Assuming that you want the results in columns F, G, and H, the solution for data set 4 would be as follows:
(Title rows have been added for clarity, they are not produced though the formulas.)

Formula 1 (in cell F2) produces the list of unique item names; this returns the UNIQUE names from column A in vertically adjacent cells:

Item
Apples
Pears
Peaches
Kiwis

Formula 2 (in cell G2) uses SUMIFS to produce the total counts associated with each item:

Item	Total
Apples	30
Pears	74
Peaches	53
Kiwis	91

Formula 3 (in cell H2) uses SUMIFS to produce the total values associated with each item, and then uses TEXT(&value,"$#,##0.00") to apply appropriate formatting.
Note: This is not the same as applying Currency or Accounting formats.

Item	Total	Value
Apples	30	$37.47
Pears	74	$107.31
Peaches	53	$62.28
Kiwis	91	$101.55

Related functions

Here are the other array formula functions supported in Workiva spreadsheets.

Support

Community

Workiva Support Center