Skip to main content

Using the SUMIF Function in Excel

Joshua earned an MBA from USF and writes mostly about software and technology.

using-the-sumif-function-in-excel

The Purpose of the SUMIF Function

The SUMIF function adds values of cells of a given range based on set criteria. Meaning you don't have to sum all the values. This function serves as a filter for just one criterion

In the following text, We'll run through the function syntax and cover a few different examples.

SUMIF Syntax

SUMIF(range, criteria, [sum_range])

The SUMIF function syntax has the following arguments:

  • range Required. This is the range of cells to be evaluated. This argument must be numbers, names, arrays, or references. Blank cells or cells with text values will be ignored.
  • criteria Required. The criteria will be in the form of an expression, number, cell reference, or text. Text criteria or any criteria that consist of logical/ mathematical symbols must be enclosed in double quotation marks
  • sum_range Optional. The actual cells to add if you don't want to add the initial range selected. This range must be the same size as the range argument.

Example 1

In the first example below I'm using the SUMIF function to sum values from D4 to D11 if corresponding values in column E are greater than 75%.

using-the-sumif-function-in-excel

I've highlighted the cells that were actually summed. It's possible to achieve the same results if the E column was to be used as the range and the D column was used as the range to sum.

using-the-sumif-function-in-excel

Example 2

This example is similar to the first one but I'm leaving out the expression and only want to sum numbers that correspond to 98%.

using-the-sumif-function-in-excel

Since there were only two values that had 98% in that record, those were the only two numbers that were summed.

using-the-sumif-function-in-excel

Example 3

In this example, I leave out that last optional argument. When doing so, the formula assumes that you want to sum the same range that is being tested. Here the only number to sum will be any number other than (not equal to) 361.


using-the-sumif-function-in-excel

In this formula I basically exclude one number.

using-the-sumif-function-in-excel

Example 4

In this final example, I use text criteria. If the value is an expense I would like them to sum. I have added an extra column to differentiate expenses and non-expenses.

using-the-sumif-function-in-excel

As you can see, only the values that were expense were considered in the sum calculation.

using-the-sumif-function-in-excel

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2022 Joshua Crowder