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.