SUMIF function is, in a way, a combination of SUM and IF statements. The formula SUMs up the values of a range of cells, IF certain criteria is met. In other words, data that pass the logic test gets to be added up (or another column of your choice, that corresponds to the same row). This formula is particularly useful for dissecting a big data table and looking at certain chunks of the data. Accounting tables, finance reports, and many more applications come to mind where you often need to battle endless rows of data, that could benefit from some advanced filtering.
Please note that SUMIF formula only accepts a single criteria to be defined. For applications with multiple criteria, you need to use the SUMIFS function instead.
Let’s take a close look at how and when to use this formula. Considering the data table below, SUMIF function can help us find the sum of base salaries of all employees from a certain department, or regional office. You can download this workbook here.
Need to look into a certain range of data? SUMIF formula also supports logical operators (>, <, <>, =), just like in an IF statement! You can utilize other formulas and add logic operators to filter your data however you want. Wildcard operators (*, ?) will come in useful for when you want to partially match data grab all similar results. Below we shaded which rows Excel will add up when you enter “>2012” and use this criteria against the Hire Date column.
The function consists of three parameters,
- range: The range of cells that you want to apply the criteria against.
- criteria: The criteria to be met to define which cells to add.
- [sum_range]: This parameter is optional. The range of cells to add. If this parameter is omitted, it uses range as the [sum_range].
In this example, we’re going to try calculating the total Base Salary of all employees from a certain State. The two parameters, range and [sum_range] will be defined on our table. The third one, criteria, will target a cell to make it a user input field. However, you can also simply enter your pre-defined criteria to make the formula simply give you a fixed result.
There are two use scenarios, one with the [sum_range] parameter, and one without. Without [sum_range], the formula will simply sum the range of values from the range parameter.
To use the formula this way, begin with entering the reference for the range of cells that will be checked against the criteria, and be summed up. In our example, we first calculate the sum of base salaries higher than $50,000.
The values from matching rows will be added as a result.
Using the SUMIF function with the [sum_range] parameter offers a bit more flexibility as to how the logic can be created.
Just like in the previous example, begin with entering the reference for the range of cells that will be checked against the criteria. Next, define the criteria to be applied. Then, add the range of numbers that are to be added together if the conditions are met.
This will give the sum of the data from rows highlighted below in darker blue.
Compatible comparison operators include,
| Operator | Description | Criteria Sample | Criteria Meaning |
| = | Equal to | “=10000” | Equal to 10000 |
| <> | Not equal to | “<>10000” | Not equal to 10000 |
| > | Greater than | “>10000” | Greater than 10000 |
| < | Less than | “>10000” | Less than 10000 |
| >= | Greater than or equal to | “>=10000” | Greater than or equal to 10000 |
| <= | Less than or equal to | “<=10000” | Less than or equal to 10000 |
| ? | Takes the place of a single character | “Admin?” | 6-character word starts by “Admin” |
| * | Can take the place of any number of characters. | “Admin*” | Any number of character word starts with “Admin” |
An important note is that the SUMIF function will return a #VALUE error if you try matching strings with more than 255 characters. It’s always a good idea to split criteria into smaller chunks or use the wildcard (*) signs to shorten the search parameter.
SUMIF is a useful formula that specializes in a niche operation. This formula can prove extremely useful when dealing with huge data tables and can greatly shorten the effort and time required to do the same thing using a combination of other formulas.
