SUMIFS interview question: summarise sales by region
Turn a raw sales list into a summary by region and product, without a pivot table. The formula interviewers expect, the $ signs that make it copy, and a sheet that grades yours.
SUMIFS function
SUMIFS adds the numbers in one range for the rows that meet every condition you give it, such as all sales in the East region above 1,000.
Available in Excel 2007 and later, Excel for the web and Google SheetsOn this page
SUMIFS syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | Required | What it means |
|---|---|---|
sum_range | Yes | The numbers to add. It comes first in SUMIFS (last in SUMIF). |
criteria_range1 | Yes | The range to test. |
criteria1 | Yes | The condition: "East", ">1000", "<>Closed", a cell such as G2, or ">"&G2. |
criteria_range2, criteria2 … | No | More pairs. A row is added only when it meets all of them. |
Examples
Total for one region
Add the amounts in column C on the rows where column B says East.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Amount | Region | Total | |
| 2 | Ana | East | 1,200 | East | 3,950 | |
| 3 | Ben | West | 800 | |||
| 4 | Cara | East | 450 | |||
| 5 | Dan | North | 1,900 | |||
| 6 | Eli | East | 2,300 |
=SUMIFS(C2:C6,B2:B6,E2) → 3,950Two conditions: region and size
Only East rows with an amount of 1,000 or more. Comparison operators go inside the quotes.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Amount | Region | Total | |
| 2 | Ana | East | 1,200 | East | 3,500 | |
| 3 | Ben | West | 800 | |||
| 4 | Cara | East | 450 | |||
| 5 | Dan | North | 1,900 | |||
| 6 | Eli | East | 2,300 |
=SUMIFS(C2:C6,B2:B6,E2,C2:C6,">=1000") → 3,500Practise it
New data, so you can't copy the answer above. We check the number your formula returns, then change the data to make sure it still holds.
Your task: In F2, total the hours logged on the project in E2.
Graded live| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Date | Project | Hours | Project | Hours | |
| 2 | 2 Nov 2026 | Atlas | 3.5 | Atlas | ||
| 3 | 2 Nov 2026 | Beacon | 2 | |||
| 4 | 3 Nov 2026 | Atlas | 4 | |||
| 5 | 4 Nov 2026 | Cobalt | 6 | |||
| 6 | 5 Nov 2026 | Atlas | 1.5 | |||
| 7 | 5 Nov 2026 | Beacon | 3 |
Write the formula in the bar above, then press Check or Enter.
Hint
The hours to add are C2:C7. The condition is on the projects, B2:B7, and the project to match is in E2.
Solution
=SUMIFS(C2:C7,B2:B7,E2) Any formula that returns the same result passes.
Common mistakes
- Putting sum_range last. That's SUMIF's order. In SUMIFS the range to add always comes first.
- Operators outside the quotes. Write ">=1000", and to use a cell, join it: ">="&G2. Writing >=G2 is an error.
- Ranges of different sizes. Every range must have the same number of rows, or SUMIFS returns #VALUE!.
FAQ
What's the difference between SUMIF and SUMIFS?
SUMIF takes one condition and puts the sum range last. SUMIFS takes one or more and puts the sum range first. SUMIFS does everything SUMIF does, so it's the one to learn.
Can SUMIFS use OR logic?
Not directly: every condition must hold. Add two SUMIFS, or use =SUM(SUMIFS(C2:C6,B2:B6,{"East","West"})).
Can I use wildcards in SUMIFS?
Yes. "M*" matches anything starting with M, and "*phone*" anything containing "phone".
Guides that use SUMIFS
Worked problems with practice sheets.
#VALUE! error in Excel: what it means and how to fix it
#VALUE! means a formula got the wrong kind of value, most often text where it needs a number. The usual causes and fixes, with a practice sheet.
Know SUMIFS? Prove it on a real sheet.
Practise SUMIFS and the functions around it in a live grid that grades your result and explains your mistakes.
Runs in your browser · Graded on your result · No install