Table of Contents
Summary
The Excel SUMIFS function adds numerical values from a range of cells when certain condition(s) are true. The SUMIFS function is useful for summing specific subsets of data with one or more specific criteria.
The SUMIFS function is very similar to the SUMIF function, however the SUMIFS function allows for multiple criteria while SUMIF only allows for a single criteria.
Formula
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Arguments (inputs)
sum_range = the range of values to be summed if the condition(s) are true
criteria_range1 = the range of data to test the criteria against
criteria1 = the criteria to test against the range of data
Only one criteria range and criteria is required, but additional criteria ranges and criteria can be added if desired.
Return value
The SUMIFS function returns the mathematical sum of the sum_range in the form of a single number, when the row or column meets the condition(s) defined by the criteria(s).
Example
In the example above the SUMIFS formula tests the criteria range C6:C16 (the sales Representative’s names) to find rows with the criteria “Smith” and sums the range of cells between F6:F16 for those rows only.
=SUMIFS(F6:F16, C6:C16, “Smith”)
There are two instances of “Smith”, one in row 7 and 15. The resulting sum of the range is returned in cell F18, a total of $1,340.
