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
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 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 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect