# How to count distinct customers per region in Excel

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.

**Excel:** `=COUNTA(UNIQUE(FILTER($A$2:$A$13,$B$2:$B$13=E2)))` (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 |
| --- | --- | --- | --- | --- | --- |
| Customer | Region |  |  | Region | Customers |
| Northwind | East |  |  | East |  |
| Contoso | West |  |  | West |  |
| Northwind | East |  |  | North |  |
| Fabrikam | North |  |  | South |  |
| Contoso | East |  |  |  |  |
| Litware | West |  |  |  |  |
| Adatum | North |  |  |  |  |
| Contoso | West |  |  |  |  |
| Fabrikam | North |  |  |  |  |
| Litware | East |  |  |  |  |
| Proseware | South |  |  |  |  |
| Northwind | West |  |  |  |  |

Formula in F2, filled to F5: `=COUNTA(UNIQUE(FILTER($A$2:$A$13,$B$2:$B$13=E2)))`

Result: 3; 3; 2; 1

## 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.

## Other ways

- `=SUMPRODUCT(($B$2:$B$13=E2)/COUNTIFS($A$2:$A$13,$A$2:$A$13,$B$2:$B$13,$B$2:$B$13))`: 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)`.

## 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)`.

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-distinct-values-per-group