# How to count unique values with a condition in Google Sheets

Filter first, then count the distinct values: `=COUNTUNIQUE(FILTER(B2:B12,C2:C12=F1))`.

**Google Sheets:** `=COUNTUNIQUE(FILTER(B2:B12,C2:C12=F1))`

**Excel:** In Excel 2021 and later, count the rows of the unique filtered list: `=ROWS(UNIQUE(FILTER(B2:B12,C2:C12=F1)))`.

## Example

Sample data (row 1 is the header):

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

Formula in F2: `=COUNTUNIQUE(FILTER(B2:B12,C2:C12=F1))`

Result: 3

## How it works

FILTER keeps the customers from the East orders, which include Northwind three times and Fabrikam twice. COUNTUNIQUE then counts each of them once: Northwind, Fabrikam and Adatum make 3. Change the region in `F1` and the count follows.

## Other ways

- `=COUNTUNIQUE(FILTER(B2:B12,(C2:C12=F1)+(C2:C12="North")))`: Add tests inside one condition for OR. Extra FILTER arguments, by contrast, all have to be TRUE. (Customers in East or North: 5.)
- `=ROWS(UNIQUE(FILTER(B2:B12,C2:C12=F1)))`: The Excel 2021 version.

## Common mistakes

- When no row matches, FILTER returns #N/A and the count is wrong or an error. Check first: `=IFNA(ROWS(UNIQUE(FILTER(B2:B12,C2:C12=F1))),0)`.
- Google Sheets also has COUNTUNIQUEIFS, but FILTER handles OR logic and calculated conditions that the IFS form can't.

## Questions

**How do I count unique values for every region at once?**

Use QUERY on the unique pairs: `=QUERY(UNIQUE(B2:C12),"select Col2, count(Col1) group by Col2",0)`.

**How do I count unique values in one month?**

Filter by date: `=COUNTUNIQUE(FILTER(B2:B12,MONTH(D2:D12)=6))`, with dates in column D.

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-unique-values-with-a-condition