Excel
All Top 50 Excel & Business Modeling Questions#20
Dynamic Aggregations on Filtered Tables with SUBTOTAL
EasyPwCInterview Question #20
Asked at PwCIn an expense report `C2:C100`, users frequently apply AutoFilters to look at individual departments. If you write `=SUM(C2:C100)`, the total remains unchanged regardless of filter selections. Write a formula in `C1` using SUBTOTAL that recalculates to show only the sum of visible rows.
Input Table: Expenses (C1:C6)
5 rows preview| Row State | Expense Amount (Col C) |
|---|---|
| Header | Formula in C1 |
| Visible | 250 |
| Filtered Out | 700 |
| Visible | 150 |
| Visible | 400 |
Expected Output Structure1 rows
| Metric | Formula Output (C1) |
|---|---|
| Visible Sum | 800 |
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 | Row State | Expense Amount (Col C) | ||||
| 2 | Header | Formula in C1 | Target [Enter Formula] | |||
| 3 | Visible | 250 | ||||
| 4 | Filtered Out | 700 | ||||
| 5 | Visible | 150 | ||||
| 6 | Visible | 400 | ||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect