How to Count Unique Values in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Beginner · 5 min read · includes 2 free exercises

In short: Use =COUNTUNIQUE(range) to count the distinct values in a range. To count only the values that appear exactly once, use =COUNTA(UNIQUE(range, FALSE, TRUE)).

Count distinct values

A list of orders may have 100 rows but only 20 different customers. COUNTUNIQUE answers the second question: how many different values are there, counting each value once however often it appears.

COUNTUNIQUE syntax

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

Per Google's help page, counting unique rows across multiple columns is not supported. The page also does not say how blank cells, text and numbers are treated, so test those cases on your own data.

A step-by-step example

Here is a list of names in cells A1:A7, with repeats.

A
1Name
2Ann
3Ben
4Ann
5Cara
6Ben
7Ann

To count the different names:

=COUNTUNIQUE(A2:A7)

This returns 3: Ann, Ben and Cara. Ann appears three times and Ben twice, but each counts once.

Count items that appear only once

COUNTUNIQUE counts every distinct value, including those that repeat. To count only the values that appear a single time, combine UNIQUE with COUNTA. The third argument of UNIQUE, exactly_once, keeps only entries without duplicates:

=COUNTA(UNIQUE(A2:A7, FALSE, TRUE))

This returns 1, because only Cara appears exactly once. Here is why:

  1. UNIQUE(A2:A7, FALSE, TRUE) returns a list of the entries that appear once: just Cara.
  2. COUNTA counts the values in that list: 1.

Count how often one value appears

For a single value, COUNTIF is simpler: =COUNTIF(A2:A7, "Ann") returns 3. See the COUNTIF guide.

Common mistakes

Try it yourself

Use the name list above (A1:A7). Type your formula into the box, starting with =, then press Check.

Exercise 1: Distinct names

Write a formula that counts how many different names are in A2:A7.

Hint: one range argument.

Exercise 2: Names that appear once

Write a formula that counts how many names in A2:A7 appear exactly once. Combine COUNTA and UNIQUE.

Hint: UNIQUE(range, FALSE, TRUE) lists the one-time entries.

What to learn next

Syntax and rules in this guide were checked against Google's Count unique values help page. See all guides.