We have gone over the SUM formula to total and aggregate data and we have gone over the IF formula to find data based on defined criteria/logic.

Now, lets combine both of these learned lessons to write and handle a `SUMIF`

function, allowing you to SUM only the data that matches your specific criteria.

Before we begin, let’s do a quick review.

### SUM Review:

A **sum** function is one of the basic Excel math functions that you can use to add data together to get a total. You can add together specific cells and ranges of cells.

`=SUM(D:D)`

for example, will SUM everything in Column D

`=SUM(D3,D4,D6,D7,D9)`

for example, will SUM only those specific cells

**IF Review:**

The IF statement allows you to setup a logical comparison for your data and identify two possible outcomes. The two examples below involve a real world logic scenario one might evaluate in order to decide what to wear based on the weather.

**If it is raining outside, wear a raincoat, otherwise wear a t-shirt
**

`=IF( raining , wear raincoat , wear t-shirt )`

**If it is raining outside , wear a raincoat, IF it is snowing outside, wear a Heavy Coat, otherwise wear a t-shirt
**

`=IF( raining, wear raincoat, IF( snowing , wear heavy coat, wear t-shirt ))`

Use the file below if you would like to follow along:

SUMIF

## SUMIF

A SUMIF combines the summing of numbers and the logic of an IF statement; summing only numbers that meet your defined logic or criteria.

Using the attached document, a standard AutoSUM on Column D would give us `215`

This information doesn’t really help us too much though. It’s good to see the total number of clothing we have sold, but what I really want to know is:

**Items sold when number is 5?
Items sold when number is greater than 25?**

**How many RainCoats have we sold?**

Let’s adjust the SUM formula to be a SUMIF formula.

**Formula Breakdown:**

`=SUMIF( `

**Range and typically your SUM Range** , **Criteria that defines what to SUM**, **Optional Cells you may want to SUM instead of the original range**)

**Application:**

**Question:** Total** **Items sold when number is 5?

Type `=SUMIF(`

into `D12`

to start your formula

Choose `D2:D11`

as your SUM Range and add a comma `,`

Type `5`

and close your formula with a parenthesis

`=SUMIF(D2:D11,5`

)

Our formula result is `15`

, which is correct as there are 3 instances where we sold 5 items, `5+5+5=15`

.

**Question:** Total** **Items sold when number is greater than 25?

Type `=SUMIF(`

into `D12`

to start your formula

Choose `D2:D11`

as your SUM Range and add a comma `,`

Type “>25” and close your formula with a parenthesis

`=SUMIF(D2:D11,>25`

)

Our formula result is `130`

, which is correct as there are 2 instances where we sold more than 25 items, `50+80=130`

.

Note that D4, 25, was not included in the SUM because our criteria was greater than 25, (>25), not greater than or equal to 25, (>=25).

**Question:** How many RainCoats have we sold

**This one is going to switch up the formula a bit as we will be adding in the 3rd SUM_RANGE option.**

Type `=SUMIF(`

into `D12`

to start your formula

Choose `C2:C11`

as your Range and add a comma `,`

Type `"RainCoat"`

and add a comma `,`

Type `D2:D11`

to select the range of cells you want to SUM

`=SUMIF(C2:C11,"RainCoat",D2:D11)`

Our formula result is 40, which is correct as there are 3 instances where we sold RainCoats, `5+25+10=40`

Categories: Excel

## Leave a Reply