## What Is SUMIFS In Excel?

The

SUMIFS in Excelis an enhanced function of theSUMIF formulain Excelthat enables us to sum up any range of data by matching several criteria. In theSUMIFS function in Excel, we can enter up to 127criteria_rangesand 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.

##### Table of contents

### Key Takeaways

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

### Syntax Of SUMIFS In Excel

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.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tuto**rials)

**–>>** **If you want to learn Excel and VBA professionally**, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from **Basic Excel to Advanced Excel, Macros, Power Query, and VBA.**

### How To Use SUMIFS Function In Excel?

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.

### Examples

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.

The steps to calculate the total salary are,

**Create a table that includes departments and regions by removing all the duplicate values. Your table should look like the one below.****Apply the SUMIFS function in the table. Open the SUMIFS function in Excel.****Select the sum_range as F2 to F21.****Select the B2 to B21 as the “criteria_range1.”****The “criteria” will be the “Department.” So, select the cell H2 and lock only the column.****The “criteria_range2” will be C2 to C21.****For this “criteria_range”, the criteria is “East,” so select the I1 cell as the reference and lock the only row here.****We now have value for the department “Web” and the region “East.”****Now, drag the formula to the remaining cells to have the result in all the cells.**

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

•**Yellow Part:**Yellow color is the first part of the formula that asks which column you want to sum. Our required column to sum is the salary column from F2:F21. We have locked this range (absolute reference) because this range should be standard when we copy-paste the formula to other cells.

•**Green Part:**It is the second part of the formula. We are considering this as our first criteria range. The first criteria we need to apply is a department column, and the department column range is B2:B21. We have locked this range (absolute reference) because this range should be standard when we copy-paste the formula to other cells.

•**Grey Part:**It is the extension of**the Green Part**. We are giving the criterion to the criteria range we have selected in the**Green Part**. Our criterion is in the cells from A23:A29. One interesting thing is we have locked only the column part ($H2) because when we move to the right side. The column should be standard, and when we are moving down, the row should change. For example, if we copy-paste the formula to the next cell, we should change $A23 to $A24.

•**Pink Part:**It is the third part of the formula. We are considering this as our second criteria range. The second criteria we must apply is the region column, and the region column range is C2:C21. We have locked this range (absolute reference) because this range should be standard when we copy-paste the formula to other cells.

•**Blue Part:**It is the extension of the**Pink Part.**We are giving the criterion to the criteria range we have selected in the**Pink Part.**Similar to the**Grey Part**of the formula.

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

### Important Things To Note

- 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 theis C1:C10 and the criteria_range is A1:A9.*sum_range* - The numerical values need not be enclosed in double-quotes. However, you need to use double quotes if we use numerical values with operators.

### Frequently Asked Questions (FAQs)

**1. How can we directly enter the SUMIFS in Excel?**

We can directly enter the **SUMIFS in Excel **in the worksheet as follows:**1)** Choose an empty cell to enter the formula.**2)** Type** =SUMIFS( **to open the formula in the cell. [Alternatively, type **=S **or **=SUM** and select the **SUMIFS** function from the Excel suggestions.]**3)** Enter the cell range for the ** sum_range** argument, and the required arguments and conditions for the

**and close the brackets.**

*[criteria_range1, criteria1], [criteria_range2, criteria2], [criteria_range3, criteria3], and so on…,***4)**Press the “

**Enter**” key to get the output.

**2. What is the limitation of SUMIFS in Excel?**

One of the limitations of** SUMIFS in Excel **is,

• It doesn’t work on closed workbooks just like the other conditional functions, which means that for the **SUMIFS** to return a result, we must keep all the referenced cell ranges’ workbooks open.

**3. Why is the SUMIFS in Excel not working?**

A few reasons the **SUMIFS in Excel **may not work are,

• There may be a non-numeric value for the ** sum_range** argument. Hence, the calculation is not accurate.

• Since we have various SUM-related functions, we may have entered the incorrect function name.

### Download Template

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

### Recommended Articles

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: –

bharat says

Thank you so much!!! This is exactly what I was looking for.

Dheeraj Vaidya says

Thanks for your kind words!