# How to count unique values in Google Sheets

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

**Google Sheets:** `=COUNTUNIQUE(B2:B12)`

**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))`.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Order | Customer |  | Customers |
| SO-501 | Northwind |  |  |
| SO-502 | Contoso |  |  |
| SO-503 | Northwind |  |  |
| SO-504 | Fabrikam |  |  |
| SO-505 | Litware |  |  |
| SO-506 | Contoso |  |  |
| SO-507 | Adatum |  |  |
| SO-508 | Fabrikam |  |  |
| SO-509 | Wingtip |  |  |
| SO-510 | Northwind |  |  |
| SO-511 | Tailspin |  |  |

Formula in D2: `=COUNTUNIQUE(B2:B12)`

Result: 7

## 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.

## Other ways

- `=COUNTA(UNIQUE(B2:B12))`: Lists the unique customers and counts the list. The same formula works in Excel 2021 and later.
- `=SUMPRODUCT(1/COUNTIF(B2:B12,B2:B12))`: Each 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-distinct-values-with-countunique