Excel
All Top 50 Excel & Business Modeling Questions#40
Calculating Distinct Customer Counts in Excel Pivot Tables via Data Model
MediumAmazonInterview Question #40
Asked at AmazonYou have an order ledger with 50,000 rows. Customer 'CUST-101' ordered 5 times. In a standard Pivot Table summarized by Category, selecting 'Count of CustomerID' returns 5 (total transactions), not 1 (unique customers). Explain how to configure the Pivot Table to show a true Distinct Count.
Input Table: OrderLog (A1:C5)
4 rows preview| Order ID | CustomerID | Category |
|---|---|---|
| ORD-1 | CUST-101 | Books |
| ORD-2 | CUST-101 | Books |
| ORD-3 | CUST-205 | Books |
| ORD-4 | CUST-308 | Electronics |
Expected Output Structure2 rows
| Category | Standard Count | Distinct Count (Unique Customers) |
|---|---|---|
| Books | 3 | 2 |
| Electronics | 1 | 1 |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order ID | CustomerID | Category | |||||||
| 2 | ORD-1 | CUST-101 | Books | Target [Enter Formula] | ||||||
| 3 | ORD-2 | CUST-101 | Books | |||||||
| 4 | ORD-3 | CUST-205 | Books | |||||||
| 5 | ORD-4 | CUST-308 | Electronics | |||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
This question demonstrates a technique that can't be graded as a single cell formula (it needs another sheet, a real PivotTable, or compares two methods side-by-side). Read the explanation below instead.