Excel functions / Maths & statistics
SUMIFS
Adds up the values that meet several conditions at once.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Arguments
sum_range | The cells to add. Note that it comes first here, unlike SUMIF. |
|---|---|
criteria_range1, criteria1 | The first range to test and its condition. |
criteria_range2, criteria2, ... | More pairs. A row is added only if it meets every condition. |
How it works
SUMIFS is SUMIF with as many conditions as you need: sales in the West and over 400, or orders from one customer within a date range.
Every condition must be true for a row to count. You can test the same column twice, which is how you total values between two numbers.
Example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | ||
| 2 | East | Ana | 500 | 700 | |
| 3 | West | Ben | 300 | ||
| 4 | East | Cal | 250 | ||
| 5 | West | Dee | 700 | ||
| 6 |
Result: 700. Only Dee is in the West with sales over 400.
Tips
- SUMIFS works with just one condition too, so many people use it everywhere instead of SUMIF.
Practise SUMIFS
Try it yourself in a real spreadsheet, with instant feedback on every answer.