Joshua earned an MBA from USF and writes mostly about software and technology.
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(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.
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%.
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.
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%.
Since there were only two values that had 98% in that record, those were the only two numbers that were summed.
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.
In this formula I basically exclude one number.
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.
As you can see, only the values that were expense were considered in the sum calculation.
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