Excel Formulas – SUMIF

Learn how to use Excel SUMIF function to compute a sum of cells matching a condition.

1. Synopsis

SUMIF(range, criteria, [sum_range])

SUMIF computes sum of cells matching a condition.

Arguments include:

range is the range of cells that criteria applies to.

criteria is the condition to be applied to the range of values for selection.

sum_range is the range of cells over the sum is to be computed.

 

2. Description

Use the SUMIF formula to compute a sum of values over a cell range that meets a criteria. For example, the following expression computes the total sales of all fruits with name matching “B*“.

=SUMIF(B11:B18,"B*", A11:A18)

The following picture shows the value being computed:

3. Sum Rows Matching an Expression

=SUMIF(B11:B18,"B*", A11:A18)

Description: Sum of fruits sold with name beginning with “B”

Result: 34

4. Select & Sum Cells Matching a Constant

=SUMIF(C11:C18,"Tom", A11:A18)

Description: Sum of fruits sold by “Tom”

Result: 52

5. Select Rows with Condition in a Cell

The criteria need not be a constant. It can be a cell reference. In that case, it would pick up the value from the referenced cell. In the following, we compute salaries during a year where the year comes from cell C19.

=SUMIF(A2:A16,C19,E2:E16)

6. Cell Containing Expression

Of course, the cell can contain an expression rather than a constant value.

7. Skip Summing Cell Range

Of course if you are summing the same range of cells used for criteria selection, you can skip the last argument.

=SUMIF(E2:E16, "> 1000000")

Summary

Use Excel formula SUMIF() to compute a sum of cells which match a specific condition. You can specify a constant or an expression for the matching condition. You can also reference the matching value from a cell.