The SUMIFS function
Adds the cells that meet every one of several conditions. The sum range comes first.
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)- sum_range
- The cells to add.
- criteria_range1
- The cells the first condition is checked against.
- criteria1
- The first condition.
- criteria_range2, criteria2 (optional)
- More range and condition pairs, up to 127.
Examples
E1
=SUMIFS(1C2:C6,2A2:A6,3"North",4B2:B6,5"Shoes")| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 920 | |
| 2 | North | Shoes | 500 | ||
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |
In plain words: Add up 1the sales in C2:C6 on the rows where 2the region in A2:A6 is 3North and 4the product in B2:B6 is 5Shoes.
E1=SUMIFS(C2:C6,A2:A6,"South",C2:C6,">200")
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 300 | |
| 2 | North | Shoes | 500 | ||
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |
Practice SUMIFS
Read the guide
Questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition and puts the sum range last. SUMIFS takes one or more conditions and puts the sum range first.