Excel
All Top 50 Excel & Business Modeling Questions#12
Filtering Transaction Records with COUNTIFS Multi-Criteria
EasyFlipkartInterview Question #12
Asked at FlipkartIn an operations log `A2:D200`, Column B contains Delivery Status ('Delivered', 'Delayed', 'Cancelled') and Column D contains Order Value. Write a formula in cell `F2` to count the number of orders that are 'Delayed' AND have an Order Value greater than or equal to $500.
Input Table: DeliveryLog (A1:D5)
4 rows preview| Order ID (Col A) | Status (Col B) | City (Col C) | Value (Col D) |
|---|---|---|---|
| ORD-1 | Delayed | Delhi | 620 |
| ORD-2 | Delivered | Mumbai | 800 |
| ORD-3 | Delayed | Bangalore | 350 |
| ORD-4 | Delayed | Kolkata | 510 |
Expected Output Structure1 rows
| Criteria | Result (F2) |
|---|---|
| Delayed orders >= $500 | 2 |
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 ID (Col A) | Status (Col B) | City (Col C) | Value (Col D) | ||
| 2 | ORD-1 | Delayed | Delhi | 620 | Target [Enter Formula] | |
| 3 | ORD-2 | Delivered | Mumbai | 800 | ||
| 4 | ORD-3 | Delayed | Bangalore | 350 | ||
| 5 | ORD-4 | Delayed | Kolkata | 510 | ||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect