Build a sales report in five minutes with SUMIFS
SUMIFS adds up only the rows that match your conditions. Learn the argument order, criteria tricks and how to build a summary table that updates itself.
Totals that answer questions
On this page
Start with one condition: SUMIF
SUMIF adds the values in one column where another column meets a condition. Read it as “sum Sales where Region is North”.
Add more conditions with SUMIFS
SUMIFS takes the sum range first, then pairs of range and criteria. Every condition must be true for a row to count.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)Criteria tricks worth memorising
Criteria are text, so comparison operators go inside quotes. To compare with a cell, join the operator and the reference with &.
| Criteria | Meaning | Result on the data above |
|---|---|---|
| ">1000" | Greater than 1,000 | 4,200 |
| "<>East" | Everything except East | 5,000 |
| "A*" | Starts with A (wildcard) | 3,600 (Rep column) |
| ">="&H1 | At least the value in H1 | Depends on H1 |
| "?????" | Exactly five characters | Matches “North”, “South” |
Turn it into a summary table
Point the criteria at a cell instead of typing “North”, and lock the data ranges with $. Now you can fill the formula down a list of regions.
The same pattern works for COUNTIFS (how many orders) and AVERAGEIFS (average order size). Same argument pairs, different maths.
Try it in a live spreadsheet
Practise these formulas in interactive lessons with instant grading.
Tom Becker
Financial analyst at FluentExcel