Excel
All Top 50 Excel & Business Modeling Questions#19
Bypassing Filtered Rows and Errors Using the AGGREGATE Function
MediumKPMGInterview Question #19
Asked at KPMGA financial data range `B2:B50` contains numbers, hidden rows (due to user filtering), and occasional `#DIV/0!` errors. Traditional `=SUM(B2:B50)` returns `#DIV/0!`. Write a formula using AGGREGATE to sum visible rows while ignoring all errors.
Input Table: RebateData (B1:B5)
4 rows preview| Row Status | Value (Col B) |
|---|---|
| Visible | 1000 |
| Visible | #DIV/0! |
| Hidden by Filter | 5000 |
| Visible | 2500 |
Expected Output Structure1 rows
| Operation | Result |
|---|---|
| Clean Sum of Visible Non-Error Rows | 3500 |
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
D2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Row Status | Value (Col B) | ||||||||
| 2 | Visible | 1000 | Target [Enter Formula] | |||||||
| 3 | Visible | #DIV/0! | ||||||||
| 4 | Hidden by Filter | 5000 | ||||||||
| 5 | Visible | 2500 | ||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
Click on target cell D2 and enter your formula above.Shortcut: Click "Load Solution" to inspect