Syntax

Before we give you the examples, let me explain the function’s syntax.

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

  • 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.Criteria 1: What is the one thing you need to SUM based on theCriteria_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_ranges and criteria pairs.

Most professionals deal with this math function in the real-time business world. The SUMIFS function in Excel will save a tremendous amount of time. In this section, we will explain to you real-time corporate examples.

How to use SUMIFs Function in Excel?

Let us now look at using the SUMIFS Function in Excel with practical examples.

Example #1

Look at the table below that includes employment history with employee name, 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 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 four different regions. Here, our first criterion is the department and the second criterion is a 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.

  • 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 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:  This is our second part of the formula. We are considering this as our first criteria range. The first criteria we need to apply are 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: 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 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: This is our third part of the formula. We are considering this as our second criteria range. The second criteria we need to apply are 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:  This 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.

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:  This is our second part of the formula. We are considering this as our first criteria range. The first criteria we need to apply are 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: 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 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: This is our third part of the formula. We are considering this as our second criteria range. The second criteria we need to apply are 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:  This 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.

Follow the below steps to calculate the total salary.

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“<>” this is a non-blank cell.

Things to Remember

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

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

  • SUMIF Not BlankSUMIF With VLOOKUPSUMIF Between Two DatesExcel Greater Than or Equal toVLOOKUP Partial Match