Contents
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.