Excel SUMIF function


The Excel SUMIF function adds numerical values from a range of cells when a condition is true. The SUMIF function is useful for summing specific subsets of data.


=SUMIF(range, criteria, [sum_range])

Arguments (inputs)

range = the range of data to test the criteria against

criteria = the criteria to test against the range of data

sum_range = [optional] the range of value to be summed if the condition (range compared to criteria) is true, if left blank the range is used as the sum_range

Return value

The SUMIF formula returns the mathematical sum of all of the numbers, cells, and/or ranges in the form of a single number, when the row or column meets the condition.


Excel SUMIF function example

In the example above the SUMIF formula tests the range C6:C16 (the sales Representative’s names) to find rows with the value “Jones” and sums the range of cells between F6:F16 for those rows only.

=SUMIF(C6:C16, “Jones”, F6:F16)

There are three instances of Jones, one in row 6, 10, and 16. The resulting sum of the range is returned in cell F18, a total of $3,010.