# How to count unique values in Excel

In Excel 365, wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(range)). In older versions, use =SUMPRODUCT(1/COUNTIF(range,range)).

**Excel:** `=COUNTA(UNIQUE(A2:A10))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=COUNTUNIQUE(A2:A10)`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Customer | Order total |  | Unique customers |
| Northwind | 420 |  |  |
| Contoso | 1250 |  |  |
| Northwind | 310 |  |  |
| Fabrikam | 880 |  |  |
| Litware | 1460 |  |  |
| Contoso | 95 |  |  |
| Northwind | 730 |  |  |
| Adatum | 515 |  |  |
| Fabrikam | 640 |  |  |

Formula in E1: `=COUNTA(UNIQUE(A2:A10))`

Result: 5

## How it works

UNIQUE returns each customer once, as a list, and COUNTA counts that list. Nothing spills on the sheet because COUNTA turns the list into one number.

Google Sheets has a function for exactly this, `COUNTUNIQUE`, and `=COUNTA(UNIQUE(A2:A10))` works there too.

## Other ways

- `=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))`: Works in every Excel version. Each name adds 1/n for each of its n rows, so every name adds up to 1. It returns #DIV/0! if the range has blank cells.
- `=ROWS(UNIQUE(A2:A10))`: The same count, using the number of rows UNIQUE returns.

## Common mistakes

- Blank cells inside the range count as one extra unique value in `UNIQUE`. Use `=COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>"")))` for a column with gaps.
- UNIQUE is not case-sensitive: "contoso" and "Contoso" are one value.
- UNIQUE needs Excel 2021 or Microsoft 365. In older versions you get #NAME?.

## Questions

**How do I count unique values with a condition?**

Filter first, then count: `=COUNTA(UNIQUE(FILTER(A2:A10,B2:B10>500)))` counts customers with at least one order over 500.

**What is the difference between unique and distinct?**

In Excel, UNIQUE(range) returns distinct values (each once). `UNIQUE(range,,TRUE)` returns only values that appear exactly once.

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

Source: https://spreadsheetpractice.com/formulas/count-unique-values