# COUNTUNIQUE function in Google Sheets

> COUNTUNIQUE counts how many different values appear in a range, ignoring blank cells.

Source: https://spreadsheetpractice.com/google-sheets/functions/countunique · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** COUNTUNIQUE counts how many different values appear in a range, ignoring blank cells.
- **Syntax:** `=COUNTUNIQUE(value1, [value2, ...])`
- **Most common use:** `=COUNTUNIQUE(B2:B8)` returns 4 (Four different customers; the blank in B5 isn't counted)
- **Works in:** Google Sheets only
- **Returns:** A number: how many distinct values there are.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet in Google Sheets mode.

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

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Order | Customer | Product |  | Question | Answer |
| 2 | 1001 | Acme Co | Widget |  | Customers | 4 |
| 3 | 1002 | Bluebird | Gadget |  | Products | 3 |
| 4 | 1003 | Acme Co | Gizmo |  | Orders with a customer | 6 |
| 5 | 1004 |  | Gizmo |  |  |  |
| 6 | 1005 | Cedar Ltd | Widget |  |  |  |
| 7 | 1006 | Bluebird | Widget |  |  |  |
| 8 | 1007 | Dune Inc | Gadget |  |  |  |

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

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
