Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to use the SUMIF function

Tags 
Summary 
The SUMIF function sums numbers conditionally: sales of red sweaters, revenue from Texas, hours billed on project X. See how it works in this short video.
Video Transcript 

In this video, we'll look at How to use the SUMIF function to sum cells that meet a single criteria.

Let's take a look.

The SUMIF function sums cells that satisfy a single condition that you supply. It takes 3 arguments, range, criteria, and sum range. Note that sum_range is optional. If you don't supply sum range, SUMIF will sum the cells in range instead.

For example, if I want to sum the cells in this range that contain the number 15, I enter B7:B12 for the range, and 15 for the criteria. There's no need to enter a sum_range since we're summing the same cells used in the condition. Excel then returns 30, two cells in the range contain 15.

If I temporarily enter another 15, that result will change.

You can add logical operators to the criteria. To sum cells with a value greater than 15, I enter the criteria of ">15" in double quotes.

You can easily move the criteria out onto the worksheet so that it's easier to change.

For example, I can enter another formula that sums cells greater than 15 by referring to B18 with the same logical operator. Notice that I need to enclose operator in double quotes and use the ampersand to concatenate with reference.

Now I can easily change the value in B18, and SUMIF will automatically adjust the criteria and calculate a new result.

In the next example, we have text values in column D, and numbers in column E.

To sum the total quantity of apples, the range is D7:D12, and the criteria is "apple", in double quotes. The sum_range is E7:E12.

Note that SUMIF is not case-sensitive and returns 15.

You can use empty double quotes to sum blanks.

SUMIF also supports wildcards. The letters "pe" plus an asterisk, will return 32, because SUMIF will sum all pears and peaches.

4 question marks will cause SUMIF to match pear, because it's the only item with 4 characters.

You can use SUMIF with dates as well.

The first example sums amounts for dates greater than January 1, 2013.

The next 3 examples use SUMIF to sum amounts to sum dates before 2012.

In the first formula uses the full date.

The second formula uses the DATE function to get the same result. Note the concatenation between operator and DATE.

In the final example, SUMIF refers to a date in cell G18, where it can be easily changed.

Notice that sort order does not affect SUMIF calculations.

Author 
Dave Bruns
Just wanted to thank you for your fantastic site. I use the site many times a week and it has helped me to solve many reporting problems and needs I have in my daily work. I have learned many new ways to use excel thanks to you. -Sanna
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course