SUMIFS with the conditions in cells
PremiumIn G2, use SUMIFS to total the Sales for the region in E2 and the product in F2. Point at the cells instead of typing the words.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Region | Product | Sales | ||
| 2 | North | Shoes | 500 | North | Bags | |||
| 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 |
Premium lesson
Unlock SUMIFS and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for job interviews
Why this works
A criterion can be a cell. With E2 and F2 as criteria the same formula answers any region and product: change F2 to Shoes and G2 shows 1010.
Common mistake: Typing the words in quotes instead of pointing at E2 and F2. The formula then only works for North and Bags.
The answer
- G2
=SUMIFS(1C2:C9,2A2:A9,3E2,4B2:B9,5F2)
In plain words: Add up 1the sales in C2:C9 on the rows where 2the region in A2:A9 is 3the region in E2 and 4the product in B2:B9 is 5the product in F2.