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)."
Syntax guide.
Parameters
The range of cells to sum.
The first range evaluated with the criteria.
The condition that must be met in criteria_range1.
The second range evaluated.
The second condition that must be met.
Interactive formula builder.
Fill in the input arguments below to construct your custom formula string. Copy it directly to paste into your Excel spreadsheets.
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 Gotchas
- The sum_range and all criteria_range parameters MUST be identical in shape and dimensions, or SUMIFS will return a #VALUE! error.