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