COUNTUNIQUE function in Google Sheets
COUNTUNIQUE counts how many different values appear in a range, ignoring blank cells. It answers questions like how many customers ordered this month or how many products sold, without building a list of unique values first.
- Works in
- Google Sheets only
- Returns
- A number: how many distinct values there are.
Syntax
=COUNTUNIQUE(value1, [value2, ...])
| Argument | Required? | What it is |
|---|---|---|
value1 | Required | A range or value to check, such as B2:B100. Add more ranges or values as extra arguments; they are counted together. |
Examples
Each result is calculated from the sample sheet by our formula engine in Sheets mode, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =COUNTUNIQUE(B2:B8) | 4 | Four different customers; the blank in B5 isn't counted |
| F3 | =COUNTUNIQUE(C2:C8) | 3 | Three different products across seven orders |
| F4 | =COUNTA(B2:B8) | 6 | For comparison: COUNTA counts every filled cell, repeats included |
Try it live
This is the sample sheet. Change any value or edit a formula, and the results update.
Practice COUNTUNIQUE
Graded instantly in Sheets mode, then re-checked on changed data.
How many customers?
F2, count how many different customers appear in B2:B10. In F3, count the different customers in the East region only.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| The same name is counted twice | A trailing space makes "Acme Co " different from "Acme Co". Clean the column first: =COUNTUNIQUE(ARRAYFORMULA(TRIM(B2:B8))). |
| The count is one too high | The header is probably inside the range. Start below it: B2:B8, not B1:B8. |
| There is no place for a condition | COUNTUNIQUE has no criteria argument. Filter first, =COUNTUNIQUE(FILTER(B2:B8,C2:C8="Widget")), or use COUNTUNIQUEIFS. |
Tips
- Count unique values that meet a condition by putting FILTER inside: =COUNTUNIQUE(FILTER(B2:B8,C2:C8="Widget")).
- To see the distinct values themselves rather than how many there are, use UNIQUE.
COUNTUNIQUE in Excel
Excel has no COUNTUNIQUE. In Excel 2021, 2024 and Microsoft 365, =COUNTA(UNIQUE(B2:B8)) gives the same count when the range has no blanks; older versions need SUMPRODUCT with COUNTIF.
Questions
How do I count unique values in Google Sheets?
Use =COUNTUNIQUE(A2:A100). Blank cells are ignored, and each different value is counted once.
How do I count unique values with a condition?
Use COUNTUNIQUEIFS, or put FILTER inside COUNTUNIQUE: =COUNTUNIQUE(FILTER(B2:B8,C2:C8="Widget")).
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine in Sheets mode.