Excel
All Top 50 Excel & Business Modeling Questions#18
Implementing OR Logic Inside SUMIFS Using Array Constants
MediumEYInterview Question #18
Asked at EYIn a transaction sheet `A2:C500`, Region is in Col A ('North', 'South', 'East', 'West'), Channel is in Col B ('Online', 'Retail'), and Sales is in Col C. Write a single formula in `E2` to sum sales where Region is either 'North' OR 'South' AND Channel is 'Online'.
Input Table: RegionalSales (A1:C5)
5 rows preview| Region (Col A) | Channel (Col B) | Sales (Col C) |
|---|---|---|
| North | Online | 1000 |
| South | Retail | 500 |
| South | Online | 1500 |
| East | Online | 800 |
| North | Online | 700 |
Expected Output Structure1 rows
| Filter Criteria | Total Online Sales (E2) |
|---|---|
| (North OR South) AND Online | 3200 |
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 | Region (Col A) | Channel (Col B) | Sales (Col C) | |||
| 2 | North | Online | 1000 | Target [Enter Formula] | ||
| 3 | South | Retail | 500 | |||
| 4 | South | Online | 1500 | |||
| 5 | East | Online | 800 | |||
| 6 | North | Online | 700 | |||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect