The SUMIF function

Want more?

The SUMIF function allows you add cells that meet a criteria that you provide.

For example, in this formula, if B2 is greater than 100, add C2; if B3 is greater than 100, add C3; and so on.

The syntax of the SUMIF function is: SUMIF(range, criteria,[sum_range]). Range is the range of cells that you want to evaluate using the criteria, such as B2 through B6.

Criteria defines what the cells in the range are evaluated against, such as greater than 100.

Sum_range are the actual cells to add if their corresponding cells in the range match the criteria, such as C2 through C6.

Brackets around sum_range indicates it is optional.

If sum_range isn't used, the cells in the range are both evaluated by the criteria and added, if they match the criteria.

Let's try it out.

We are going to create a formula that adds Property values that are over $160,000.

We start with an = sign, SUMIF, open parenthesis, select the range, comma, put the criteria in quotes (in this case greater than 160,000), and press Enter.

And the sum of the Property values over $160,000 is $900,000.

This is an example where sum_range is omitted.

The cells in the range, A2 through A5, are both evaluated by the criteria and added, if they match the criteria.

In this example, if A2 is greater than 160,000, add A2; if A3 is greater than 160,000, add A3; and so on.

Now, we are going to create a formula that adds the Commission for Property values that are over $160,000.

We start with an = sign, SUMIF, open parenthesis, select the range, comma, put the criteria in quotes, comma, select the sum_range, and then press Enter.

And the Commissions for Property values over $160,000 is $63,000.

In this example, sum_range is defined.

If A2 is greater than 160,000, add B2; if A3 is greater than 160,000, add B3; and so on.

We can also evaluate text in a SUMIF function.

Let's create a formula that totals Sales for Fruits.

As always, we start the formula with an = sign, SUMIF, open parenthesis, select the range, comma, put the criteria in quotes (in this case ""fruits""), comma, select the sum_range, and then press Enter.

And we see that the Sales Total for Fruits is $2000.

In this example, if A2 is equal to Fruits, add C2; if A3 is equal to Fruits, add C3; and so on.

We can use the ? and * wildcards as criteria arguments in a SUMIF function.

A question mark matches any single character and an asterisk matches any sequence of characters.

Let's add the Sales for Foods ending in "es". = sign, SUMIF, open parenthesis, select the range, comma, put the criteria in quotes (in this case asterisk ES), comma, select the sum_range, and then press Enter.

And the total Sales for Foods ending in "es" is $4300.

In this example, if the last word in B2 ends in "es", add C2 ; if the last word in B3 ends in "es", add C3; and so on.

Now, you have a good idea of what is involved in adding numbers in Excel 2013.

Of course, there is more to learn.

So check out the course summary at the end for more information about adding numbers in Excel 2013.