How to count unique values with criteria in Excel
Formula (Excel and Google Sheets)
=COUNTA(UNIQUE(FILTER(A2:A13,B2:B13=G1)))Filter the rows that meet the condition, then count the unique values left: =COUNTA(UNIQUE(FILTER(A2:A13,B2:B13=G1))).
- Result on the example
- 5 in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
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.
=COUNTA(UNIQUE(FILTER(A2:A13,B2:B13=G1)))Result5
A2:A1312 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …A range: 12 cells in one column.
B2:B1312 rows × 1 col: "Desk", "Chair", "Chair", "Desk", …A range: 12 cells in one column.
G1"Desk"The value in G1.
B2:B13=G1{12 rows × 1 col} TRUE, FALSE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
FILTER(A2:A13,B2:B13=G1){6 rows × 1 col} "Northwind", "Fabrikam", "Contoso", "Northwind", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:A1312 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", … - condition1
B2:B13=G1{12 rows × 1 col} TRUE, FALSE, FALSE, TRUE, …
- range
UNIQUE(FILTER(A2:A13,B2:B13=G1)){5 rows × 1 col} "Northwind", "Fabrikam", "Contoso", "Litware", …UNIQUE: Distinct rows of a range. Spills.- array
FILTER(A2:A13,B2:B13=G1){6 rows × 1 col} "Northwind", "Fabrikam", "Contoso", "Northwind", …
- array
COUNTA(UNIQUE(FILTER(A2:A13,B2:B13=G1)))5COUNTA: Counts cells that are not empty.- value1
UNIQUE(FILTER(A2:A13,B2:B13=G1)){5 rows × 1 col} "Northwind", "Fabrikam", "Contoso", "Litware", …
- value1
In Google Sheets
The same formula works in Google Sheets: =COUNTA(UNIQUE(FILTER(A2:A13,B2:B13=G1))). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B2:B13=G1)/COUNTIFS(A2:A13,A2:A13,B2:B13,B2:B13)) | 5 | 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) | 5 | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count unique values with criteria
G2, count how many different customers bought the product in G1.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.