How to count unique customers by region without counting repeat orders
Count each customer once within a region, even if they placed several orders. Use two summaries to turn an order list into regional customer counts.
Three orders from the same customer should still count as one customer. To count customers by region, first group by Region and Customer ID, then count those groups by Region. Each customer will count once in every region where they ordered.
Six orders do not mean six customers
The fictional order list below contains repeat orders from C01 in East and C03 in West. C01 also ordered in West, so that customer will count once in each region.
| Region | Customer ID |
|---|---|
| East | C01 |
| East | C01 |
| East | C02 |
| West | C01 |
| West | C03 |
| West | C03 |
The desired regional counts are East=2 and West=2. Across the entire business there are only three unique customers: C01, C02 and C03.
Group by region and customer, then count customers
- Load and select the sheet, open Refine results, and add Summarize.
- Under Group by, select Region and Customer ID one at a time. Keep the default Count method and enter Orders in Output field name. This makes one row per region–customer combination.
- Add a second Summarize step. This time, group only by Region.
- Keep Count, enter Customers in Output field name, and choose Apply. Do not sum the Orders field from the preceding step.
The first step produces four combinations: East–C01, East–C02, West–C01 and West–C03. The second step counts two of those rows in each region.
Each region has two customers, but the overall count is three
| Region | Customers |
|---|---|
| East | 2 |
| West | 2 |
The regional counts add up to four because C01 is counted in both East and West. Only three different customers appear across the whole list. Label the report “Customers by region” so the sum is not mistaken for a company-wide customer count.
How do I count customers across all regions instead?
Group only by Customer ID in the first summary, then leave the second summary's grouping empty and use Count. That gives three for this example.
Add a Customer ID Is not empty filter before the first summary if missing IDs should not count. Use an ID that stays the same for each customer, rather than a name that several people may share. Check inconsistent capitalization or spaces in the source so one customer's ID is not counted as several values.
Try it with your own files
Start with JOIN SHEET ↗Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.
Example last verified:
Sources
Simple pivot table to count unique values — Stack Overflow
The fictional order list illustrates the grouped unique-count problem in this question.
