How it works.
SUMIFS adds up values in a range that meet one or more conditions. Unlike the older SUMIF function, SUMIFS lets you apply up to 127 criteria, and the range containing the values to sum is always the first argument.
💡 In simple terms.
Adds up all numbers in a range that meet one or more conditions (e.g., adding sales only for West region and Apple product).
"Think of this like a smart piggy bank. It only adds up coins that match all your criteria (like only summing quarters that are shiny)."
Formula syntax.
Standard Excel Notation=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)[like_this] are optional; all other arguments are required.Formula arguments.
Every parameter accepted by the SUMIFS function is detailed below.
The range of cells to sum.
Example value:C2:C6The first range evaluated with the criteria.
Example value:A2:A6The condition that must be met in criteria_range1.
Example value:"Fruit"The second range evaluated.
Example value:B2:B6The second condition that must be met.
Example value:">100"Simple example.
Step-by-Step ScenarioFilter & Sum Regional Sales
Sum the Total Sales of a specific Product in a specific Region.
| Region | Product Name | Sales Amount | Query Region | Result Total |
|---|---|---|---|---|
| West | Apple | 1200 | West | — |
| East | Orange | 950 | Apple | — |
| West | Orange | 800 | — | — |
=SUMIFS(C2:C6, A2:A6, "West", B2:B6, "Apple")Evaluates SUMIFS over the sample table cells, placing the verified output in cell E2.
Formula generator.
Full Generator WizardCustomize the argument fields below to generate a tailored SUMIFS formula ready to paste directly into your worksheet.
=SUMIFS()Expected result behavior.
Returns "Calculated output" based on sample evaluation criteria and active cell references.
Best Practices
- You can use logical operators (e.g. ">100", "<=50") and wildcards (e.g. "App*", "*east*") inside your criteria.
- Lock all range parameters with `$` so you can drag the formula down without shifting lookup ranges.
Common Mistakes
- The sum_range and all criteria_range parameters MUST be identical in shape and dimensions, or SUMIFS will return a #VALUE! error.
Platform & version compatibility.
Verified platform compatibility matrix based on actual spreadsheet calculation engine specifications.
Core standard function supported across all modern and legacy Microsoft Excel versions (365, 2021, 2019, 2016, 2013, 2010), as well as Excel for the Web and Mac.
100% compatible with Google Sheets. Uses identical argument order, data types, and standard calculation logic.
Frequently asked questions.
Technical Q&AWhat is the difference between SUMIF and SUMIFS?↓
SUMIF supports only one condition and puts the sum_range last: =SUMIF(range, criteria, [sum_range]). SUMIFS supports up to 127 criteria pairs and places the sum_range first: =SUMIFS(sum_range, criteria_range1, criteria1, ...).
Why does SUMIFS return 0 instead of the expected total?↓
SUMIFS requires ALL conditions to be met simultaneously (AND logic). If no single row satisfies all criteria, it returns 0. Also verify that numeric comparison operators are quoted (e.g., ">100" instead of >100).
Do criteria ranges in SUMIFS need to match the sum range dimensions?↓
Yes. Every criteria_range in SUMIFS must have the exact same number of rows and columns as the sum_range; otherwise, Excel will return a #VALUE! error.