SUMIFS with two conditions
Step 1 of 3
SUMIFS adds up the rows where every condition holds. Unlike SUMIF, the range to add up comes first, followed by pairs of a range and its condition.
Click Continue when you're ready.
Already know it? Type the formula straight into E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | North shoes | ||
| 2 | North | Shoes | 500 | |||
| 3 | South | Shoes | 300 | |||
| 4 | North | Bags | 250 | |||
| 5 | North | Shoes | 420 | |||
| 6 | South | Bags | 180 | |||
| 7 | North | Bags | 150 | |||
| 8 | South | Shoes | 610 | |||
| 9 | North | Shoes | 90 | |||
| 10 |
Need help?
Why this works
SUMIFS adds the rows where every condition holds: North in column A and Shoes in column B, giving 500 + 420 + 90 = 1010. Unlike SUMIF, the sum range is the first argument.
Common mistake: Putting the sum range last, as in SUMIF. In SUMIFS it always comes first.
The answer
- E2
=SUMIFS(1C2:C9,2A2:A9,3"North",4B2:B9,5"Shoes")
In plain words: Add up 1the sales in C2:C9 on the rows where 2the region in A2:A9 is 3North and 4the product in B2:B9 is 5Shoes.
SUMIF takes one condition and puts the sum range last. SUMIFS takes several conditions and puts the sum range first.
+20 points