The SUMIFS function in Excel is a powerful tool for summing values based on multiple criteria. Unlike its counterpart, SUMIF, which applies a single condition, SUMIFS allows you to specify multiple conditions for more precise calculations. In this guide, we'll explore how to use the SUMIFS function, providing tips and error handling methods to ensure accuracy. Our examples will reference a workbook containing employee data, which you can research by clicking the the SUMIFS function and download by clicking the button below. Join us as we delve into the world of SUMIFS and discover its versatility in Excel.
SUMIFS Function in Excel: Syntax
You will see a breakdown of the formula for SUMIFS function in excel as you start typing it in Excel. Let’s take a look at its components in more detail.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- sum_range: The range of cells to sum.
- criteria_range1: The range of cells you want to check for criteria1 against.
- criteria1: The criteria that is applied to criteria_range1 to determine which cells to add.
- [criteria_range2, criteria2]: Optional. Additional ranges and their associated criteria pairs. You can enter up to 127 range/criteria pairs.
Example
In this guide, we will provide you that valuable insights into leveraging the SUMIFS function for your data analysis needs in Excel. Join us as we explore practical examples and unlock the full potential of the SUMIFS function.
Let’s see it in action. In SUMIFS formula, multiple criteria support can be used with different columns (1) or with the same column (2).
- To add up the overtime paid for employees who live in a certain state and certain department, by checking for the criteria in both state, and department columns
- To add up the total salaries paid for employees who were hired between specified dates, by searching greater than and less than a given value on the same column
Assume that we have an employee list like the one shown below and we want to calculate the sum of salaries for the employees who,
- were hired between 1/1/2006 and 12/31/2011
AND
- are working in the Administration department
These three columns will be our sum range and criteria ranges,
- Column G: Salary (sum)
- Column B: Hire Date (criteria)
- Column J: Department (criteria)
Let’s begin creating the formula. The first argument is sum_range. In our example, we want to add whatever values are pulled from the G2:G21 range (Base Salary).
Next, we’re going to define the criteria_range and criteria argument pairs. To implement the “hire date between dates” condition, we’re going to need to use the hire date column (B2:B21) twice, for both “greater than start date” and “less than end date”. The first part of this argument is greater than or equal to 1/1/2006, so we add this into the formula by entering “>=1/1/2006”.
Now, let’s add the “less than or equal to end date” with same hire date column (B2:B21). We need to enter this date as “<=12/31/2011”.
Last criteria is the department check. We’re looking for the employees from the administration department so we must enter “Administration” for the final lookup value.
We’re done! Our sample criteria matched with the employees below highlighted in yellow.
SUMIFS Function in Excel: Tips
In essence, SUMIFS function in Excel is a pretty straightforward function that gives you a lot of flexibility to build data models. However, there are of course good and bad practices. Let’s take a look at some general rules of thumb.
- Use same number of rows and columns for sum and criteria range
- Bad: =SUMIFS(G2:G15,F2:H10,">2014",J2:J20,"IT") –> 15-2=13 vs. 10-2=8 not good!
- Good: =SUMIFS(G2:G11,F2:F11,">2014",J2:J11,"IT") –> 11-2=9 vs. 11-2=9 good!
- You can use these comparison operators when creating a SUMIFS formula,
| 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 with “Admin” |
| * | Can take the place of any number of characters. | “Admin*” | A word with any number of characters starts with “Admin” |
SUMIFS Function in Excel: Common Issues and Errors
#VALUE!
SUMIFS function in Excel returns incorrect results when you use it to match strings longer than 255 characters or to the string #VALUE!.
TRUE and FALSE
TRUE and FALSE values in sum_range are evaluated as numbers. While TRUE is evaluated as 1, FALSE is evaluated as 0. This can give unexpected results when they are used in other formulas or arguments.
