This section and the next will cover the advanced summing functions available in Excel.
The SUMIF and SUMIFS functions enable you to sum values in a range of cells if they meet specified criteria. Values that don’t meet the criteria are excluded from the sum.
The syntax for SUMIF is as follows:
SUMIF(range, criterion, [sumrange])
range: range of cells to evaluate
criterion: number, expression, function etc. that indicates which cells should be added
sumrange: (optional) the cells to add, if different from “range”
The criterion can be a number, an expression, a function or a text string. SUMIF restricts the data being summed according to a single criterion. SUMIFS allows you to specify multiple criteria. The syntax is:
SUMIFS(sum_range, criterion_range1, criterion1, [criterion_range2, criterion2], …)
sum_range: range of cells to add
criterion_range1: the range that is evaluated against criterion1
criterion1: number, expression, function etc. that indicates which cells in criterion_range 1
should be added
criterion_range2, criterion 2: (optional) additional criterion and the corresponding range
You may enter additional criteria as needed.
Worksheet 03m contains some measured stress data in column B as well as the number of times the stress was at a certain level during the measurement period in column C. We will use SUMIF to calculate the percent of time that the stress was above a certain level. In order to do that, you’ll first calculate the percent of time represented by each count in column C. To do this, we’ll divide each count by the sum of all the counts. Enter =C6/SUM($C$6:$C$3) in cell D6. If you prefer, you can select the cells with the mouse – remember to type F4 after you select the range for all the counts to create an absolute reference. This way the values in the denominator won’t change as we fill the formula into the rest of the column. Select cell D6 and double-click the fill handle.
Now column D displays the percentage of time that this location was at each stress level. In column G, we’ll use SUMIF to calculate the percentage of time that the stress was greater than certain amounts. Enter =SUMIF( in cell G6. The range to be evaluated is in column B, so select that range (click the first cell and type Ctrl-Shift-Down Arrow), then type F4 to make it an absolute reference.
So far you should have:
=SUMIF($B$6:$B$32
Add a comma after the first argument. For the second argument, we have to build the criterion. We’ll use the concatenate function (&) to form a string joining together the greater than symbol (“>”) and the location of the cell that we’re comparing against (F6). Therefore, the second argument will be: “>”&F6. It’s important to include the quotation marks.
At this point your formula will be:
=SUMIF($B$6:$B$32,”>”&F6
Again, add a comma after the argument. Lastly, we need to select the data to be summed – the percent data in column D. Click cell D6, type Ctrl-Shift-Down Arrow, followed by F4. Add a close parenthesis. Your formula should be:
=SUMIF($B$6:$B$32,”>”&F6,$D$6:$D$32)
This formula tells Excel to check if the value in column B is greater than the value in column F, and if so sum the corresponding percentages from column D.
Type Enter, select the cell again, and double-click the fill handle. This is the resulting table:
It’s obvious from the data that the stress level is greater than zero 100% of the time. To check the other values, you can highlight the cells in column D that are greater than 10,000. Excel will automatically display the sum of the highlighted cells in the lower left border of the window:
The sum should match the value calculated by the SUMIF function.
Worksheet 03n contains the same data, but poses a different question: what percentage of the time was the stress between two values? We’ll add all the percentage values between the two limits shown on the worksheet (6,000-20,000 psi), including the limits themselves. To answer this question, we’ll solve use the SUMIFS function
with two criteria, one for the lower limit and one for the upper limit. In the previous problem, we simply used the greater than (>) operator. In this example, we will include the limits in the criteria to see how to use the ≥ and ≤ operators in Excel.
You may have noticed that the SUMIFS function has a different order for its arguments. The range to be summed comes first. Enter =SUMIFS( in cell K5. Select the percentage data in column D.
The second argument is the range of the first criterion. In this case, the criterion will be based on the stress data in column B. At this point your formula should be:
=SUMIFS(D7:D33,B7:B33,
The third argument, the first criterion, will be constructed similarly to the first example, but instead of “>” we will use “>=” which is Excel’s form of the ≥ operator. Use & to concatenate to the cell containing the lower limit, G5.
=SUMIFS(D7:D33,B7:B33,”>=”&G5
That completes our first criterion. We also need to add a criterion to limit the summed values for stress levels less than 20,000 (cell G6). The SUMIFS function can take additional criteria by adding arguments for the range and criterion. For this problem, the range will be the same. The criterion will simply be “<=”&G6, restricting the summed values to only those with a stress less than or equal to G6.
The final formula will be:
=SUMIFS(D7:D33,B7:B33,”>=”&G5,B7:B33,”<=”&G6)
Again, you can quickly check that this formula summed only the values that fall within the limits by highlighting the percentage values that correspond to stress between 6,000 and 20,000 psi. The value in the lower border should match:
You can adjust the limits in cells G5 and G6 and the SUMIFS function will update accordingly.
