Excel SUMIFS Function
SUMIFS adds values only when multiple conditions are all satisfied.
Use it for multi-condition totals, such as revenue by region and month, hours by employee and project, or costs for one category inside a date range.
SUMIFS syntax & arguments
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
-
1
sum_range
RequiredThe cells to add when all criteria pass.
-
2
criteria_range1
RequiredThe first range of cells to test.
-
3
criteria1
RequiredThe condition that cells in
criteria_range1must meet. It can be a number, text, cell reference, expression such as">100", or wildcard pattern. -
4
criteria_range2, criteria2
Optional RepeatableAdditional range-and-criteria pairs. Each extra criteria range must line up with
sum_range, and all criteria must pass before the matching value is added.
Example
=SUMIFS(D2:D50, B2:B50, "East", C2:C50, "Complete")
Add values in D2:D50 where B2:B50 is East and the matching cell in C2:C50 is Complete.
SUMIFS caveats
SUMIFS is the standard tool for conditional totals, but its argument order and range alignment are easy places to make mistakes.
-
The sum range comes first
Unlike SUMIF, SUMIFS starts with
sum_range, then uses range-and-criteria pairs. -
Criteria ranges must align
Every criteria range should cover the same rows or columns as
sum_rangeso each test lines up with the value being added. -
All conditions are AND logic
A value is added only when every criteria pair passes for the same row or position.
-
Operators usually need quotes
Criteria such as
">100"or"<>Cancelled"must be written as text.
Need counts instead? Use COUNTIFS when matching rows should be counted rather than summed.
Intro SUMIFS practice problem
Solve the intro problem directly here, or open it on its own page.
Advanced SUMIFS practice problems
Use SUMIFS alongside other Excel functions in realistic, less-prescriptive challenges.
