Excel
All Top 50 Excel & Business Modeling Questions#17
Partial Text Aggregation with Wildcards in SUMIFS
MediumAmazonInterview Question #17
Asked at AmazonIn an inventory table `A2:B100`, Column A contains product names like 'Apple iPhone 15 Pro', 'Apple MacBook Air', 'Samsung Galaxy S24', and 'Apple iPad'. Column B contains Units Sold. Write a formula in `D2` to sum the total units sold for all 'Apple' branded products.
Input Table: InventoryLedger (A1:B5)
5 rows preview| Product Description (Col A) | Units Sold (Col B) |
|---|---|
| Apple iPhone 15 Pro | 120 |
| Samsung Galaxy S24 | 85 |
| Apple MacBook Air | 45 |
| Google Pixel 8 | 30 |
| Apple iPad Pro | 60 |
Expected Output Structure1 rows
| Brand Search | Total Units Sold (D2) |
|---|---|
| Apple Products | 225 |
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 | Product Description (Col A) | Units Sold (Col B) | ||||
| 2 | Apple iPhone 15 Pro | 120 | Target [Enter Formula] | |||
| 3 | Samsung Galaxy S24 | 85 | ||||
| 4 | Apple MacBook Air | 45 | ||||
| 5 | Google Pixel 8 | 30 | ||||
| 6 | Apple iPad Pro | 60 | ||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect