# How to count unique values with criteria in Excel

Filter the rows that meet the condition, then count the unique values left: `=COUNTA(UNIQUE(FILTER(A2:A13,B2:B13=G1)))`.

**Excel:** `=COUNTA(UNIQUE(FILTER(A2:A13,B2:B13=G1)))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Customer | Product |  |  |  | Product | Desk |
| Northwind | Desk |  |  |  | Customers |  |
| Contoso | Chair |  |  |  |  |  |
| Northwind | Chair |  |  |  |  |  |
| Fabrikam | Desk |  |  |  |  |  |
| Contoso | Desk |  |  |  |  |  |
| Litware | Lamp |  |  |  |  |  |
| Northwind | Desk |  |  |  |  |  |
| Adatum | Chair |  |  |  |  |  |
| Contoso | Chair |  |  |  |  |  |
| Fabrikam | Lamp |  |  |  |  |  |
| Litware | Desk |  |  |  |  |  |
| Adatum | Desk |  |  |  |  |  |

Formula in G2: `=COUNTA(UNIQUE(FILTER(A2:A13,B2:B13=G1)))`

Result: 5

## How it works

FILTER keeps the customers on rows where the product is "Desk": six rows, with Northwind twice. UNIQUE removes the repeat, and COUNTA counts what's left: 5 customers.

Google Sheets also has COUNTUNIQUEIFS for this, but the FILTER version works in both apps.

## Other ways

- `=SUMPRODUCT((B2:B13=G1)/COUNTIFS(A2:A13,A2:A13,B2:B13,B2:B13))`: Works in every Excel version. Each customer-and-product pair adds up to exactly 1, and only Desk pairs are kept.
- `=IFERROR(ROWS(UNIQUE(FILTER(A2:A13,B2:B13=G1))),0)`: Returns 0 when nothing matches, where the COUNTA version returns 1.

## Common mistakes

- When no row matches, FILTER returns an error and COUNTA counts that error as 1. Use the IFERROR(ROWS(…),0) version if a product can have no sales.
- Blank customer cells inside the filtered rows count as one more unique value. Add `*(A2:A13<>"")` to the FILTER condition.
- UNIQUE and FILTER need Excel 2021 or Microsoft 365.

## Questions

**How do I count unique values with two criteria?**

Multiply the conditions inside FILTER: `=COUNTA(UNIQUE(FILTER(A2:A13,(B2:B13=G1)*(C2:C13="East"))))`.

**How do I count unique customers between two dates?**

Use the dates as the condition: `=COUNTA(UNIQUE(FILTER(A2:A13,(D2:D13>=G3)*(D2:D13<=G4))))`.

**What does COUNTUNIQUEIFS do in Google Sheets?**

It counts unique values with conditions in one step: `=COUNTUNIQUEIFS(A2:A13,B2:B13,G1)`.

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

Build a table: `=LET(p,UNIQUE(B2:B13),HSTACK(p,MAP(p,LAMBDA(x,ROWS(UNIQUE(FILTER(A2:A13,B2:B13=x)))))))` in Microsoft 365, or a pivot table with Distinct Count.

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