Crunch Data Even Faster with Excel SUMIFS Function

SUMIF function, as the name suggests, sums up the values in a range of cells, if a certain criteria is met. What if you want to compare multiple checks and add different columns? That’s where its older brother, SUMIFS, come into play. SUMIFS can do the exact same thing, but with multiple criteria. In this guide, we’re going to show you how to use the SUMIFS function and also throw in some tips and error handling methods. In our examples, we’re going to be referencing to a workbook that contains employee data. You can download it pressing the button below.

Syntax

You will see a breakdown of the formula 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

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).

1. 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
2. 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,

1. Column G: Salary (sum)
2. Column B: Hire Date (criteria)
3. 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.

Tips

In essence, this 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,

Common Issues and Errors

#VALUE!

The SUMIF function 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.