SUMIFS In Excel

The SUMIFS in Excel is an enhanced function of the SUMIF formula in Excel that enables us to sum up any range of data by matching several criteria. In the SUMIFS function in Excel, we can enter up to 127 criteria_ranges and criteria pairs.

For example, in the given dataset, we can find the sum of all the values that fall under the “Web” department of the “East” region, as shown in cell I2.

The output is derived with multiple criteria, such as the set department in a particular region. The values are retrieved first, and then the total is calculated.

• The SUMIFS in Excel helps users first get the values that satisfy the set multiple criteria, then calculates the total.
• Ensure that the retrieved values to calculate the sum, w.r.t the set criteria, are always numeric. The criteria can be of any form, such as name, wildcards, symbols, alpha-numeric, numeric values, etc.
• The only difference between SUMIF and SUMIFS is that SUMIF evaluates only one criterion, but SUMIFS in Excel can evaluate up to 127 of those.
• We know the SUMIFS doesn’t work with closed workbooks, so that we can use the SUMPRODUCT function to perform similar calculations, and it works on closed workbooks.

The syntax of the SUMIFS Formula is,

The arguments of the SUMIFS Formula are,

• sum_range: The cells or range of cells that you want to SUM. For example, the A1:A20 range.
• criteria_range1: The column which includes the first criteria values.
• criteria1: The first condition to retrieve the value from.
• criteria_range2: The second column, which includes the second criteria values.
• criteria2:  The second condition to retrieve the value from.

We can insert the SUMIFS Function in Excel in multiple ways. One way is by inserting it from the “Function Library”, as follows:

First, keep the dataset ready → select the “Formulas” tab → go to the “Function Library” group → click the “Math & Trig” option drop-down → select the “SUMIFS” function as shown below.

The “Function Arguments” window opens. Enter the cell range in the “Sum_range” and the “Criteria_range1” fields, and click “OK”, as shown below.

We will consider some examples for the SUMIFS Function in Excel.

Example #1

The table below includes the employment history with the employee names, the department they belong to, years of service in the company, and their salaries.

Now, first, look into the SUMIF example. Using SUMIF, the function calculates the total salary for the Marketing department.

It gives the output as shown below:

The above image shows the total salary for the marketing department. The SUMIF function can take only one set of criteria.

What if you want to sum up the total salary for the marketing department in the northern region? In multiple criteria-based situations, we can use the Excel SUMIFS function to calculate the salary.

Example #2 - Multiple Criteria (2) SUMIFS in Excel

To calculate the total salary for each department across four different regions, let’s take our first criterion as the department and the second criterion as the region.

Example #3 - Multiple Criteria (3) SUMIFS in Excel

In the previous example, we have seen two criteria in one SUMIFS Excel example. Assume you want to calculate the total salary for each department across four different regions if the year of service is greater than 5 years. Here, our first criterion is a department, the second criterion is the region, and the third criterion is a year of service.

The steps to calculate the total salary are,

It is the same as the above example.

We need to continue the formula after two criteria.

Now, look at the detailed explanation of the formula part by part.

We have already discussed the Yellow Part, Green Part, Grey Part, Pink Part, and Blue Part. The only thing we have added here is Years’ Service as our third criteria column, and “>5” is the criteria we are giving.

Since we need to add the salary if the years of service are greater than 5 years, we have used the operator symbol (>). In the SUMIFS function, we can use an operator symbol to get the job done.

Below are some of the operator symbol examples.

• “>5”: Greater than 5 years.
• “>=5”: Greater than or equal to 5 years.
•  “=5”: Equal to 5 years.
• “<5”: Less than 5 years.
• “<=5”: Less than or equal to 5 years.
• “<>”: It is a non-blank cell.

• We can apply the SUMIFS function in Excel to 127 criteria ranges. All the ranges should be of the same length. For example, Excel will throw an error if the sum_range is C1:C10 and the criteria_range is A1:A9.
• The numerical values need not be enclosed in double-quotes. However, you need to use double quotes if we use numerical values with operators.

This article must help understand SUMIFS in Excel with its formulas and examples. You can download the template here to use it instantly.

This article is a guide to SUMIFS in Excel. Here we find sum of retrieved numeric values that satisfy multiple criterias, examples, downloadable excel template. You may learn more about Excel from the following articles: -

• SUMIF Not Blank
• SUMIF With VLOOKUP
• SUMIF Between Two Dates
• Excel Greater Than or Equal to
• VLOOKUP Partial Match