Spreadsheet Practice

How to count unique values in Google Sheets

Google Sheets formula

=COUNTUNIQUE(B2:B12)

Use COUNTUNIQUE on the range: =COUNTUNIQUE(B2:B12) counts each different value once and ignores blank cells.

Result on the example
7 in D2
Excel
See below

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

How it works

COUNTUNIQUE counts distinct values: Northwind appears three times but counts once, and so do the other repeat customers. Eleven orders came from seven customers. Empty cells are not counted.

=COUNTUNIQUE(B2:B12)

Result7

  1. B2:B1211 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …

    A range: 11 cells in one column.

  2. COUNTUNIQUE(B2:B12)7

    COUNTUNIQUE: Counts distinct values, ignoring blanks.

    • value1B2:B1211 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …

In Excel

Excel has no COUNTUNIQUE. In Excel 2021 and later use =COUNTA(UNIQUE(B2:B12)); in older versions =SUMPRODUCT(1/COUNTIF(B2:B12,B2:B12)).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=COUNTA(UNIQUE(B2:B12))7Lists the unique customers and counts the list. The same formula works in Excel 2021 and later.
=SUMPRODUCT(1/COUNTIF(B2:B12,B2:B12))7Each customer's rows add up to exactly 1. Works in every version of both apps, but fails on blank cells.

Common mistakes

  • The header is counted if the range starts in row 1. Start at row 2.
  • A trailing space makes "Contoso " a different customer from "Contoso". Clean the column with TRIM first.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count unique values

In D2, count how many different customers placed the orders in B2:B12.

Questions

How do I count unique values with a condition?

Put FILTER inside: =COUNTUNIQUE(FILTER(B2:B12,C2:C12="East")).

How do I list the unique values instead of counting them?

Use =UNIQUE(B2:B12).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.