The SUMIFS function in Excel
SUMIFS adds up numbers for the rows where all of your conditions are true, like revenue for the Leeds warehouse in the Garden line, or February units from Bristol. It's the workhorse behind most summary tables in finance and operations, and the function interviewers expect when a question has more than one "where".
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
| sum_range | The cells to add. Note it comes first, unlike in SUMIF. |
|---|---|
| criteria_range1 | The first range to test. |
| criteria1 | The condition for criteria_range1, like "Leeds" or ">=80". |
| criteria_range2, criteria2, ... | Optional. More range and condition pairs. Every range must be the same size as sum_range. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
In E2, use SUMIFS to total the ad Spend for the Social channel in Mar.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Spend | Social, March | |||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 | |||||||
| 9 | |||||||
| 10 | |||||||
| 11 |
Tip: Start with the Spend column, then add one condition pair for Channel and one for Month.
Examples
=SUMIFS(E2:E9,A2:A9,"Leeds",C2:C9,"Garden")→ 7800Adds revenue for rows where the warehouse is Leeds and the product line is Garden.
=SUMIFS(D2:D9,B2:B9,"Feb",A2:A9,"Bristol")→ 170Switches the sum range to Units: total February units shipped from Bristol.
=SUMIFS(E2:E9,D2:D9,">=80",D2:D9,"<=120")→ 13750Tests the same column twice to add revenue only for shipments of 80 to 120 units.
Example data
| 1 | Warehouse | Month | Line | Units | Revenue |
|---|---|---|---|---|---|
| 2 | Leeds | Jan | Garden | 120 | 3600 |
| 3 | Leeds | Jan | Kitchen | 80 | 4000 |
| 4 | Bristol | Jan | Garden | 95 | 2850 |
| 5 | Leeds | Feb | Garden | 140 | 4200 |
| 6 | Bristol | Feb | Kitchen | 60 | 3000 |
| 7 | Bristol | Feb | Garden | 110 | 3300 |
| 8 | Leeds | Feb | Kitchen | 75 | 3750 |
| 9 | Bristol | Jan | Kitchen | 50 | 2500 |
Common mistakes
Sum range in the wrong place
SUMIFS starts with the cells to add:
SUMIFS(E2:E9, ...). Copying the SUMIF habit of putting it last gives wrong totals or #VALUE!.Ranges of different sizes
If
sum_rangeisE2:E9, every criteria range must also cover rows 2 to 9, or Excel returns #VALUE!.Expecting OR between conditions
All conditions must be true at once. For Leeds or Bristol, add two SUMIFS together.
Totals come back as 0
Usually a condition doesn't match anything: a typo, a trailing space, or a number stored as text in the sum range. Test each condition on its own with COUNTIFS.
FAQ
How do I use SUMIFS with dates between two values?+
Use the date column twice with ">="&start and "<="&end, for example =SUMIFS(C2:C50,A2:A50,">="&F1,A2:A50,"<="&F2).
Can SUMIFS use OR logic?+
Not within one formula. Add one SUMIFS per alternative, like SUMIFS(...,"Leeds")+SUMIFS(...,"Bristol").
Should I use SUMIF or SUMIFS?+
SUMIFS works for one condition too, so many analysts use it everywhere to avoid remembering two argument orders.
Lesson
Master SUMIFS step by step: COUNTIFS and SUMIFS
Count and sum with several conditions at once, and learn why SUMIFS puts the sum range first.