SUMIFS: total with two conditions
One region and one product at the same time.
Task:
Sales wants coffee sales by region, ignoring tea. In G2 give the total for North coffee and in G3 for South coffee.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of SUMIF
0/4The same formula in the other shapes it takes at work.
- SUM with Multiple Values
Sum values from multiple non-adjacent cells or ranges.
CoreIntermediate - SUMIF: total one category
Add up only the rows that belong to the group you were asked about.
Intermediate - SUMIFS: total with two conditions
One region and one product at the same time.
Advanced - SUMIF with a comparison
Add up only the values above a line — the criterion can be a comparison, not just a match.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Product | Value | Region | Coffee total | |
| 2 | B-201 | North | Coffee | 320 | North | ||
| 3 | B-202 | North | Tea | 140 | South | ||
| 4 | B-203 | South | Coffee | 275 | |||
| 5 | B-204 | North | Coffee | 410 | |||
| 6 | B-205 | South | Tea | 190 | |||
| 7 | B-206 | South | Coffee | 365 | |||
| 8 | B-207 | North | Tea | 95 |
What this exercise teachesMay contain the answer
SUMIFS adds a row only when every condition holds, which is the AND you mean. Its argument order differs from SUMIF — sum range first — and that one difference causes most of the mistakes people make with it.