The purpose of the SUMIF function is to add the specified cells by a given criterion.

excel icon

 

Using SUMIF, you combine 2 functions in one: SUM and IF, you don’t have to worry about 2 syntaxes and a nested formula is not needed.

 

 

 

The SUMIF syntax is basic: = SUMIF (range, criteria, sum_range)

Here is a short description of each argument:

  • Range: the array you want to evaluate and where you will apply the criterion.
  • Criteria: selecting a criterion you instruct Excel which cells to add and, by omission, which cells to leave out.
  • Sum_range: the array of numbers from which, by applying the criterion, SUMIF will choose the values to be added.

Let me give you a brief example:

This is a standard, simplified sales performance tracker for a team of sales representatives. We want to find out how much each individual generated in Q1.

sumif - example

sumif - example

In cell D13 we will insert this formula: =SUMIF(C2:C10,C13,D2:D10).

The criterion is “Peter” (C13). We apply the criterion in the Range, the group of cells where SUMIF should look for “Peter”, i.e. array C2:C10. Finally, we define the Sum_range, or the group of cells from where SUMIF should pick, based on the criterion, the cells to be added, in our case D2:D10, i.e. Peter’s numbers (we want to leave Tim and Marc out).

Remember!!! If you want to drag the formula down, in order to calculate Tim’s and Marc’s revenues, you need to define the Range and the Sum_Range as absolute references. The formula in cell D13 will be: =SUMIF($C$2:$C$10,C13,$D$2:$D$10), and the one in cell D14 will be: =SUMIF($C$2:$C$10,C14,$D$2:$D$10). Notice that, because we have set absolute references, with the help of the “$” sign, once we move down a cell, the Range and the Sum_range remain the same, only the Criteria changes.

That’s it for the SUMIF formula, stay tuned for future Excel tutorials from www.onebitzero.com, hope you enjoyed this one and, until next time, Stay Geek!

JC