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
C1fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Row State | Expense Amount (Col C) | Target [Enter Formula] | |||||||
| 2 | Header | Formula in C1 | ||||||||
| 3 | Visible | 250 | ||||||||
| 4 | Filtered Out | 700 | ||||||||
| 5 | Visible | 150 | ||||||||
| 6 | Visible | 400 | ||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
Click on target cell C1 and enter your formula above.Shortcut: Click "Load Solution" to inspect