How to count distinct customers per region in Excel
Formula (Excel and Google Sheets)
=COUNTA(UNIQUE(FILTER($A$2:$A$13,$B$2:$B$13=E2)))Next to each region, count the unique customers in that region: =COUNTA(UNIQUE(FILTER($A$2:$A$13,$B$2:$B$13=E2))), filled down.
- Result on the example
- one result per row in
F2, filled down toF5 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F5. Change any input and the result updates, or edit the formula to experiment.
How it works
For each region, FILTER pulls out the customers on that region's rows, UNIQUE drops repeats, and COUNTA counts the rest. East has four orders but only three customers, because Northwind ordered twice.
The order columns are locked with $; only E2 moves as you fill down. The same customer can count in more than one region, as Contoso does in East and West.
=COUNTA(UNIQUE(FILTER($A$2:$A$13,$B$2:$B$13=E2)))Result3
$A$2:$A$1312 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …A range: 12 cells in one column.
$B$2:$B$1312 rows × 1 col: "East", "West", "East", "North", …A range: 12 cells in one column.
E2"East"The value in E2.
$B$2:$B$13=E2{12 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
FILTER($A$2:$A$13,$B$2:$B$13=E2){4 rows × 1 col} "Northwind", "Northwind", "Contoso", "Litware"FILTER: Rows of a range that meet every condition. #N/A when none match.- range
$A$2:$A$1312 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", … - condition1
$B$2:$B$13=E2{12 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …
- range
UNIQUE(FILTER($A$2:$A$13,$B$2:$B$13=E2)){3 rows × 1 col} "Northwind", "Contoso", "Litware"UNIQUE: Distinct rows of a range. Spills.- array
FILTER($A$2:$A$13,$B$2:$B$13=E2){4 rows × 1 col} "Northwind", "Northwind", "Contoso", "Litware"
- array
COUNTA(UNIQUE(FILTER($A$2:$A$13,$B$2:$B$13=E2)))3COUNTA: Counts cells that are not empty.- value1
UNIQUE(FILTER($A$2:$A$13,$B$2:$B$13=E2)){3 rows × 1 col} "Northwind", "Contoso", "Litware"
- value1
In Google Sheets
The same formula works in Google Sheets: =COUNTA(UNIQUE(FILTER($A$2:$A$13,$B$2:$B$13=E2))). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(($B$2:$B$13=E2)/COUNTIFS($A$2:$A$13,$A$2:$A$13,$B$2:$B$13,$B$2:$B$13)) | 3, 3, 2… | Works in every Excel version: each customer-and-region pair adds up to 1, and only the current region's pairs are kept. |
Common mistakes
- A pivot table's Count shows orders, not distinct customers. Tick "Add this data to the Data Model" when you create it, then choose Distinct Count.
- A region with no orders makes FILTER return an error, which COUNTA counts as 1. Wrap it as
IFERROR(ROWS(UNIQUE(FILTER(…))),0).
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 distinct customers per region
F2, count the distinct customers who ordered in the region in E2, then fill down to F5.Questions
How do I build the region list automatically?
Put =UNIQUE(B2:B13) in E2 to list each region once, then fill the count formula down beside the list.
Is there a shortcut in Google Sheets?
Yes: =COUNTUNIQUEIFS($A$2:$A$13,$B$2:$B$13,E2).
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.