Excel
All Top 50 Excel & Business Modeling Questions#11
Multi-Condition Revenue Aggregation with SUMIFS and Date Boundaries
MediumAmazonInterview Question #11
Asked at AmazonYou have a sales ledger in `A2:C100` with Order Date in Col A, Category in Col B, and Revenue in Col C. Given Category in `E2`, Start Date in `F2`, and End Date in `G2`, write a formula in `H2` to calculate total revenue for that category between those dates inclusive.
Input Table: SalesLedger (A1:C5)
4 rows preview| Order Date (Col A) | Category (Col B) | Revenue (Col C) |
|---|---|---|
| 2026-01-05 | Electronics | 1200 |
| 2026-01-15 | Electronics | 850 |
| 2026-01-20 | Furniture | 400 |
| 2026-02-02 | Electronics | 2100 |
Expected Output Structure1 rows
| Category (E2) | Start Date (F2) | End Date (G2) | Total Revenue (H2) |
|---|---|---|---|
| Electronics | 2026-01-01 | 2026-01-31 | 2050 |
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
H2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order Date (Col A) | Category (Col B) | Revenue (Col C) | |||||||
| 2 | 2026-01-05 | Electronics | 1200 | Electronics | 2026-01-01 | 2026-01-31 | Target [Enter Formula] | |||
| 3 | 2026-01-15 | Electronics | 850 | |||||||
| 4 | 2026-01-20 | Furniture | 400 | |||||||
| 5 | 2026-02-02 | Electronics | 2100 | |||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
Click on target cell H2 and enter your formula above.Shortcut: Click "Load Solution" to inspect