What SUMIFS Does
SUMIFS adds up values in a range where multiple conditions are all met. Unlike SUMIF, which handles only one criterion, SUMIFS lets you stack as many conditions as you need. It is the standard way to build filtered totals in reporting and financial models.
Syntax
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
| Parameter | Description |
|---|---|
| sum_range | The range of values to sum. |
| criteria_range1 | The first range to evaluate against a condition. |
| criterion1 | The condition for criteria_range1. |
| criteria_range2, criterion2 | Additional range/condition pairs. All conditions must be true for a row to be included. |
Basic Examples
Example 1: Sum Sales by Region and Product
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Revenue |
| 2 | East | Widget | 3500 |
| 3 | West | Widget | 2100 |
| 4 | East | Gadget | 4200 |
| 5 | East | Widget | 1800 |
=SUMIFS(C2:C5, A2:A5, "East", B2:B5, "Widget")
Result: 5300 (3500 + 1800) — only rows where region is East AND product is Widget.
Example 2: Sum Values in a Date Range
Total expenses between two dates:
=SUMIFS(D2:D500, C2:C500, ">="&DATE(2026,1,1), C2:C500, "<="&DATE(2026,3,31))
Result: Sum of all expenses from January 1 to March 31, 2026.
Example 3: Sum with a "Not Equal" Condition
Sum revenue for all regions except "West":
=SUMIFS(C2:C100, A2:A100, "<>West")
Advanced Examples
Dynamic Dashboard Totals
Build a summary table that updates when you change a filter cell. If G1 has the region and G2 has the quarter:
=SUMIFS(Revenue!D:D, Revenue!A:A, G1, Revenue!B:B, G2)
Change G1 to "North" and G2 to "Q2", and the total recalculates instantly. This pattern powers most dashboard-style reports.
Using Wildcards with SUMIFS
Sum revenue for all product names containing "Pro":
=SUMIFS(C2:C200, B2:B200, "*Pro*", A2:A200, "East")
The wildcard *Pro* matches "Pro Plan", "ProMax", "Enterprise Pro", etc.
Common Mistakes
- Putting sum_range first. In SUMIF, the sum range is the third parameter. In SUMIFS, it is the first. Mixing up the order is extremely common and produces wrong results without any error message.
- Mismatched range sizes. Every criteria range must have the same number of rows as the sum range. If
C2:C100is your sum range, don't useA2:A50as a criteria range. - Not quoting operator criteria. Conditions like
>=must be in quotes and joined with cell references via&. For example:">="&D1, not>=D1.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "sum revenue where region is East and product is Widget" — and the formula is generated for you. Install SheetAI to try it free.