SUMIF is a built-in Excel function that sums the values in a range of cells based on a given criteria. It is a very versatile function that can be used in a variety of ways.
How SUMIF works
SUMIF works by first searching for the criteria you specify in the first argument. If it finds a match, it sums the values in the range you specify. If it doesn’t find a match, it returns 0.
The optional third argument can be used to specify a different range for the criteria. This can be useful if the criteria is not in the same range as the values you want to sum.
Syntax of SumIf function
The syntax of the function: =SUMIF(range,criteria,[sum_range])
The range argument is the range of cells you want to evaluate against the given criteria. This specifies where Excel looks to find cells that meet the criteria you’ve set.
The criteria argument is used to determine the criteria against which the function will be summarized cells. It is possible to use the criterion in terms of numbers, Boolean expression, or text.
Sum_range is the optional range of cells to sum. If omitted, the cells in range that meet the criteria are summed.
Sumif function examples
Usage of SUMIF explains the following example.
To calculate the sum of people employed in Arizona, the cell type in the formula:
=SUMIF(A2:A11,”Arizona”,C2:C11)
As you can see, in this case the range of cells, where we check the criterion (A2: A11) is different from that which we value sum (C2: C11). Please also note that the criterion for entering text, enclose it in quotation marks. In the case of the criterion being the number of quotes can be omitted.
To calculate the total income in units employing more than 450 people, we use the formula:
=SUMIF(C2:C11,”>450″,D2:D11)
This formula checks cells C2:C11 for values greater than 450 and sums the corresponding incomes from D2:D11. Like the search text, logical expressions apprehend in quotation marks.
