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.