Home » Excel » How to use the SUMIF Function

How to use the SUMIF Function

Navigate

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
SUM IF SUM

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

Leave a Reply

%d bloggers like this: