UNIQUE and COUNTUNIQUE functions in Google Sheets

UNIQUE returns each different value in a range once; COUNTUNIQUE counts how many different values there are.

A column of real data repeats itself: the same customers, cities and products again and again. UNIQUE reduces it to one of each. COUNTUNIQUE answers "how many different ones?" without making the list.

Syntax

=UNIQUE(range, [by_column], [exactly_once])

=COUNTUNIQUE(value1, [value2], ...)

Argument	What it is
`range`	The range to take values from.
`by_column`	TRUE to compare columns. Leave it out, or FALSE, to compare rows.
`exactly_once`	TRUE to return only the values that appear exactly once.

Examples

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=UNIQUE(B2:B13)
Result:
Asha
Ravi
Meena
Kabir
Each salesperson once, in the order they first appear.
=UNIQUE(C2:C13)
Result:
North
South
East
The regions.
=SORT(UNIQUE(D2:D13))
Result:
Bag
Notebook
Pen
Wrapped in SORT for alphabetical order.
=COUNTUNIQUE(B2:B13)
Result: 4
How many different salespeople, in one function.
=COUNTUNIQUE(C2:C13)
Result: 3
How many different regions.
=UNIQUE(FILTER(D2:D13,C2:C13="North"))
Result:
Notebook
Bag
Pen
The different products sold in the North.
=JOIN(", ",SORT(UNIQUE(C2:C13)))
Result: East, North, South
The list of regions as one piece of text.

How UNIQUE works

UNIQUE reads down the range and returns each value the first time it appears. The result spills into the cells below. The order is the order of first appearance. Wrap it in SORT for an alphabetical list.

The list is live. A new value added to the data appears in it at once. Compare that with Data, Data clean-up, Remove duplicates, which deletes rows from the data itself and must be run again each time.

Several columns

Given two or more columns, UNIQUE compares whole rows and returns each different combination. =UNIQUE(C2:D13) lists every pairing of region and product that occurs.

Values that appear only once

Set the third argument to TRUE and UNIQUE returns only the values that have no duplicate at all. =UNIQUE(A2:A100, FALSE, TRUE) finds one-time customers, or the entries in a list that were not repeated.

COUNTUNIQUE

COUNTUNIQUE returns the number of different values in one step. It ignores empty cells. In Excel, the same count needs COUNTA(UNIQUE(...)), so this is a small convenience that Sheets has to itself.

To count different values among the rows that meet a condition, use COUNTUNIQUEIFS: =COUNTUNIQUEIFS(B2:B13, C2:C13, "North") is the number of different salespeople who sold in the North.

A summary with two formulas

In H2: =UNIQUE(B2:B13) lists the salespeople.
In I2: =ARRAYFORMULA(SUMIF(B2:B13, H2:H5, F2:F13)) puts each one's total beside the name.

For a summary that grows by itself as names are added, QUERY with group by does both steps in one formula.

A drop-down list from the data

Put =SORT(UNIQUE(D2:D)) in a spare column. Then use Data, Data validation with "Dropdown (from a range)" pointing at that column. The choices offered always match the values in use.

How values are compared

Capital and small letters count as different in UNIQUE: "Asha" and "ASHA" are listed separately.
Spaces count. "Asha" and "Asha " are two values. If a name appears twice in a unique list, look for a stray space and clean the data with TRIM.
A number and the same digits stored as text are different values.
An empty cell in the range produces an empty entry in the list. Filter blanks out first: =UNIQUE(FILTER(A2:A, A2:A<>"")).

Common mistakes

Typing in the cells where the list must spill, which gives #REF!.
Including the heading in the range.
Expecting the list to be sorted.
Counting with COUNTA(UNIQUE(...)) on a range with blanks, which counts the blank as a value. COUNTUNIQUE does not.

In Excel

Excel 2021 and Microsoft 365 have UNIQUE with the same three arguments. In Excel, UNIQUE treats capital and small letters as the same. Excel has no COUNTUNIQUE or COUNTUNIQUEIFS.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.