Have you ever needed to sum only specific values in a range of data, and exclude others that did not meet the necessary criteria?

The SUMIF and SUMIFS functions enable you to do exactly this – sum values in a range of cells if they meet specified criteria. Values that don’t meet the criteria are excluded from the sum.

SUMIF and SUMIFS Functions in Excel

The syntax for SUMIF is as follows:

SUMIF(range, criteria, [sum_range])

where:

range: range of cells to evaluate

criteria: number, expression, function etc. that indicates which cells should be added

sum_range: (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.

The SUMIFS function, on the other hand, allows you to specify multiple criteria. The syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

where:

sum_range: range of cells to add

criteria_range1: the range that is evaluated against criteria1

criteria1: number, expression, function etc. that indicates which cells in criteria_range 1 should be added

criteria_range2, criteria2: (optional) additional criterion and the corresponding range

You may enter additional criteria as needed.

Using SUMIF to Calculate a Sum Based on Criteria

The worksheet shown below 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. To do that, we’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.

To 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, use SUMIF to calculate the percentage of time that the stress was greater than certain amounts.

First, 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 we have:

=SUMIF($B$6:$B$32

For the second argument, we need to build the criterion.

Use the concatenate function (&) to form a string joining together the greater than symbol (“>”) and the location of the cell that’s being compared against (F6). Therefore, the second argument will be: “>”&F6.

It’s important to include the quotation marks.

At this point the formula will be:

=SUMIF($B$6:$B$32,”>”&F6

Again, add a comma after the argument. Lastly, 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. The final 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.

Using SUMIFS to Calculate the Total Between Two Values

But what if we want to evaluate what percentage of the time the stress was between two values? We can add all the percentage values between the two limits shown on the worksheet below (6,000-20,000 psi), including the limits themselves.

To answer this question, we’ll solve using 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, we can quickly verify 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.

Wrap Up

As you can see, the SUMIF and SUMIF functions are great functions that allow you to sum only the values in a range of data that meet specific criteria. The example I’ve shown above is only one way to use these versatile functions. I’m sure you’ll encounter many ways to use them in your projects as well!