Quick, clean, and to the point

How to use the SUMIF function

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 a 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 are 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 a 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. Note 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 the SUMIF will 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, the criteria is "apple", and the sum_range is E7:E12.

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

You can use empty double quotes to sum blanks.

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

4 question marks will cause SUMIF to match pear, since 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 Jan 1, 2013.

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

In the first formula, the full date is entered.

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.

Note that sort order does not affect SUMIF calculations.


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.