The SUMIFS function in Excel
SUMIFS adds up the numbers in rows that pass several tests at once.
What SUMIFS does
It works like SUMIF with extra conditions. The important difference is the order: in SUMIFS the cells to add come first, then each pair of cells to test and the condition to apply.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- sum_range
- The cells to add. In SUMIFS this comes first.
- criteria_range1
- The first set of cells to test.
- criteria1
- The first condition, such as "North".
- criteria_range2, criteria2
- Optional. More pairs. A row must pass every pair.
Worked example
| A | B | C | |
|---|---|---|---|
| 1 | Product | Region | Sales |
| 2 | Lamp | North | 120 |
| 3 | Mug | South | 80 |
| 4 | Desk | North | 150 |
| 5 | Chair | East | 90 |
| 6 | Plant | North | 60 |
| 7 | North over 100 | 270 |
C7 =SUMIFS(C2:C6,B2:B6,"North",C2:C6,">100")
Two North rows have sales above 100: 120 + 150 = 270.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Finance
Total spend for one team in one month.
Sales & Ops
Add up sales by region and product.
HR
Total hours for one department above a threshold.
Common mistakes
- Putting the range to add last, as SUMIF does. In SUMIFS it must come first.
- Ranges of different sizes, which causes a #VALUE! error.
- Leaving the quotes off a comparison such as ">100".
The SUMIFS course
- 1Add up with two conditionsFree practice belowPractise
- 2Sum between two datesComing soon
- 3Sum by region and productComing soon
- 4Wildcards in SUMIFSComing soon
- 5Budget vs actual by monthComing soon
- 6Common SUMIFS mistakesComing soon
Common questions
Does SUMIFS need every condition to be true?
Yes. A row is added only if it passes all the tests.
Can I use a cell reference in the criteria?
Yes. Join a comparison and a cell with an ampersand, for example ">"&E1.