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
E2fx
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order Date (Col A) | Category (Col B) | Revenue (Col C) | |||
| 2 | 2026-01-05 | Electronics | 1200 | Target [Enter Formula] | ||
| 3 | 2026-01-15 | Electronics | 850 | |||
| 4 | 2026-01-20 | Furniture | 400 | |||
| 5 | 2026-02-02 | Electronics | 2100 | |||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect