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 |
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 valuesMembers lesson
- 3Sum for two groupsMembers lesson
- 4Wildcards in SUMIFSMembers lesson
- 5Sum against a target cellMembers lesson
- 6Everything except one groupMembers lesson
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.
Related functions
Unlock every lesson
Every function has a free lesson. Members get all the lessons, fresh exercises every time and the weekly leaderboard. £7 a month or £49 a year, and you can cancel at any time.