SUMIFS Function: syntax, examples, mistakes and translation notes
Adds values matching multiple conditions.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Arguments
sum_range
Cells whose values will be added.
criteria_range1
First range to test.
criteria1
Condition applied to the first criteria range.
additional criteria
Optional range/criteria pairs for more rules.
Worked examples
=SUMIFS(D:D,A:A,H2,B:B,"Open")
Sums column D where column A matches H2 and column B is Open.
=SUMIFS(D:D,C:C,">="&J2,C:C,"<="&K2)
Sums D for dates in C between J2 and K2.
Practical tips
- Keep every criteria range aligned to the same rows as the sum range.
- Build comparison criteria with operators joined to cells, such as ">="&J2.
- Put changing business thresholds in cells instead of hard-coding them repeatedly.
Common mistakes
- Using ranges with different starting rows or sizes.
- Forgetting that dates are numeric serials and comparing them with ambiguous text dates.
Related functions
Frequently asked questions
What does SUMIFS do?
Adds values matching multiple conditions.
Why might a SUMIFS formula return an unexpected result?
Using ranges with different starting rows or sizes. Start by testing the smallest referenced range or condition independently before adding wrappers or fallbacks.
Check the complete formula
Function syntax is only one part of a working workbook. Paste the finished expression into the Formula Checker for structural warnings, the Formatter for readability or the Translator when a supported locale mapping is involved.
Testing note: examples are educational. Spreadsheet versions, locale settings, tables, named ranges and workbook data can change behavior, so validate important formulas with representative data.