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.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
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.
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).
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.