SUMIFS adds numbers where multiple conditions are all true. Here is the complete guide — syntax, 6 real examples, common mistakes and FAQ.
SUMIFS adds numbers where multiple conditions are all true at the same time. It is the multi-condition version of SUMIF — when you need to filter by more than one criterion before summing, SUMIFS is the formula.
Common real uses: total sales for one product in one region, sum expenses for one department in one month, add up hours for one employee on one project.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | Description |
|---|---|
| sum_range required | The column of numbers to add up. This comes FIRST — unlike SUMIF where it comes last. |
| criteria_range1 required | The first column to check the condition against. |
| criteria1 required | The first condition to match. |
| criteria_range2, criteria2 optional | Additional conditions. All must be true for a row to be included. Up to 127 pairs. |
In SUMIF, sum_range is the third argument. In SUMIFS, sum_range is the first argument. This is the most common mistake when switching between the two.
=SUMIFS(C2:C100, A2:A100, "Coffee", B2:B100, "North")Column A = product, column B = region, column C = sales. Only rows where A="Coffee" AND B="North" are summed.
=SUMIFS(C2:C100, A2:A100, F1, B2:B100, F2)F1 and F2 hold the filter values. Change them and all linked SUMIFS formulas update instantly — this is how dashboards work.
=SUMIFS(C2:C100, A2:A100,
">="&DATE(2026,1,1),
A2:A100, "<="&DATE(2026,3,31))
Sum values where the date falls in Q1 2026. Use & to join the operator with the DATE formula.
=SUMIFS(C2:C100, A2:A100, "Coffee", C2:C100, ">"&AVERAGE(C2:C100))Combine text and numeric conditions freely. Here: sum Coffee sales that were above the overall average.
=SUMIFS(D2:D100, A2:A100, "Coffee", B2:B100, "North", C2:C100, "2026")Product = Coffee AND Region = North AND Year = 2026. Every condition must be true for a row to count.
=SUMIFS(C2:C100, A2:A100, "<>Refund", B2:B100, "North")Sum all North region sales that are NOT refunds. Use <> for "not equal to" in criteria.
| Situation | Use |
|---|---|
| One condition | SUMIF or SUMIFS (both work) |
| Two or more conditions | SUMIFS only |
| Sum range = criteria range | SUMIF (cleaner syntax) |
SUMIFS works for one condition too, making it a universal replacement for SUMIF. Many experienced Excel users just use SUMIFS all the time and forget SUMIF exists.
The most common mistake. In SUMIF the sum range is last. In SUMIFS it is first. If you get a wrong result, check your argument order.
All ranges (sum_range, criteria_range1, criteria_range2 etc.) must be the same size. A2:A100 and B2:B50 in the same formula returns a #VALUE! error.
SUMIFS uses AND logic — all conditions must be true. For OR logic (condition1 OR condition2), add two SUMIFS results together: =SUMIFS(...)+SUMIFS(...).
ExcelPro has SUMIFS exercises in every specialist track — realistic multi-condition scenarios, free to start.
Try SUMIFS exercises →