SUMIFS in excel is a conditional formula to calculate the sum, as the same suggests it performs the addition operator on a range of cells when they fulfill multiple if condition or multiple criteria provided in the function, this is an inbuilt function in excel and are widely used as conditional statements.

## SUMIFS in Excel

SUMIFS in excel is useful when we want to SUM numbers based on multiple criteria. SUMIFS counterpart SUMIF is used to sum numbers based on single criteria.

In this article, we will look at SUMIFS function in excel along with practical examples of its usage with multiple criteria.

### SUMIFS in Excel – Syntax

Before I give you the examples let me explain you the syntax of the function.

The syntax of SUMIFS includes **SUM_RANGE, Criteria_Range1, and Criteria1, Criteria_Range2, and Criteria2 **and so on.

**SUM_RANGE:**The cells or range of cells that you want to SUM. For Example: A1:A20 range.**Criteria_Range1:**The column, which includes first criteria values.**Criteria 1:**What is the one thing you need to SUM based on the**Criteria_Range2:**The second column, which includes second criteria values.**Criteria 2:**What is the one thing you need to SUM based on the**Criteria_Range1**and

**Note: **In the SUMIFS function in excel, we can enter up to 127 criteria range and criteria pairs.

In real time business world, most of the professionals deal with this math function. SUMIFS Function in excel will contribute to saving a tremendous amount of time. In this section, I will explain to you real-time corporate examples.

### How to use SUMIFs Function in Excel?

Let us now look at the usage of SUMIFS Function in excel with practical examples.

#### SUMIFS in Excel Example #1

Look at the below table that includes employment history with employee name, the department they belong to, no., of years 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 given below:

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

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

**Example #2 – Multiple Criteria (2) SUMIFS in Excel**

Assume you want to calculate the total salary for each department across 4 different regions. Here our first criterion is department and the second criterion is region.

Follow the below steps to calculate the total salary.

**Step 1: **Create a table that includes departments and region by removing all the duplicate values. Your table should look like the below one.

**Step 2: **Apply the SUMIFS function in the table. Open SUMIFS function in excel.

**Step 3: **Select the sum range as F2 to F21.

**Step 4: **Select the B2 to B21 as the criteria range1.

**Step 5: **Criteria will be your department. So select the cell H2 and lock only the column.

**Step 6: **Second Criteria Range2 will be C2 to C21.

**Step 7: **For this criteria range, criteria is Zone so select I1 cell as the reference and lock the only row here.

**Step 8: **Now we have value for the department Web & for the region East.

**Step 9: **Now simply Drag the formula to the remaining cells to have resulted 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 is asking which column you want to sum. Our required column to sum is salary column and the salary column range is 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: **This is our second part of the formula. We are considering this as our first criteria range. First criteria we need to apply are department column and 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: **This 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 are moving to the right side column should be standard and when we are moving down row should change. For example, if we copy paste the formula to the next cell $A23 should be changed to $A24.

**Pink Part: **This is our third part of the formula. We are considering this as our second criteria range. Second criteria we need to apply are the region column and 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: **This is the extension of **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’s in one SUMIFS excel example. Assume you want to calculate the total salary for each department across 4 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**.

Follow the below steps to calculate the total salary.

This is the same as the above example we just need to continue the formula after two criteria’s.

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

We have already discussed **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’ service is greater than 5 years we have used the operator symbol (>). In 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**“<>”**this is a non-blank cell.

**Things to Remember**

- SUMIFS in excel can be applied up to 127 criteria ranges.
- All the ranges should be of the same length. If your sum range is C1:C10 and your criteria range is A1:A9 excel will throw an error.
- Numerical values need not be enclosed in double quotes. However, if you are using numerical values with operators then you need to use double quotes.
- The only difference between SUMIF and SUMIFS is where SUMIF evaluates only one criterion but SUMIFS in excel can evaluate up to 127 of those.

### Recommended Articles

This has been a guide to SUMIFS in Excel. Here we discuss SUMIFS syntax and how to use SUMIFS Function with multiple criteria in excel along with examples and downloadable excel template. You may learn more about excel from the following articles –

- SUMIF Not Blank | Examples
- Explanation of SUMIF With VLOOKUP
- SUMIF Between Two Dates
- Excel Greater Than or Equal to
- Using SUMPRODUCT with Multiple Criteria
- DSUM Function
- SUM in Excel
- Using SUMPRODUCT in Excel

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion