Let’s take a look. The SUMIF function sums cells that satisfy a single condition that you supply. It takes three 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’re summing the same cells used in the condition. Excel then returns “30” because 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 cell B18 with the same logical operator. Notice that I need to enclose the operator in double quotes and use the ampersand to concatenate the 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. Four question marks will cause SUMIF to match pear because it’s the only item with four characters. You can use SUMIF with dates as well. The first example sums amounts for dates that are greater than January 1, 2013. The next three examples use SUMIF to sum amounts for dates before 2012. The first formula uses the full date. The second formula uses the DATE function to get the same result. Note the concatenation between the operator and DATE. In the final example, SUMIF refers to a date in cell G19, where it can be easily changed. Notice that sort order does not affect the SUMIF calculation.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
