Function reference · Math & aggregation
SUMIFS
Adds up numbers that meet several conditions at once.
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
SUMIFS is SUMIF with more than one test — sales in the West, in Q3, above 1000. Conditions combine with AND.
Arguments
| Argument | Required | What it does |
|---|---|---|
sum_range |
Yes | The numbers to add. Comes first, unlike SUMIF. |
criteria_range1 |
Yes | The range the first condition tests. |
criterion1 |
Yes | The first condition. |
Worked example
| Region | Rep | Sales |
|---|---|---|
| West | Ana | 1200 |
| East | Ben | 900 |
| West | Cara | 1500 |
| North | Dan | 600 |
| East | Eve | 1100 |
=SUMIFS(C2:C6, A2:A6, "West", C2:C6, ">1000")Returns 2700.
West rows with sales above 1000 — both of Ana's and Cara's figures qualify.
Gotchas
- The sum range comes FIRST here and LAST in SUMIF. Swapping them is the most common cause of a zero total.
- All ranges must be the same height.
- Conditions are AND only. There is no OR — use two SUMIFS added together, or QUERY.