SUMIFS in Excel

SUMIFS is an enhanced version of the SUMIF formula in Excel that enables you to sum any range of data by matching several criteria. For example, if you have sales values based on city-wise across multiple months, then using the SUMIFS function, we can get the total sales value for the particular city in the particular month. In this case, City & Month are the criteria to arrive at the sales value.

Syntax

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

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

Sumifs Formula in Excel
  • SUM_RANGE: The cells or range of cells that you want to SUM. For Example, 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 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.the

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.

You can download this SUMIFS Function in Excel Template here – SUMIFS Function in Excel Template

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.

SUMIFS Example 1

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

SUMIFS Example 1-1

It gives the Output as given below:

SUMIFS Example 1-2

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 the department and the second criterion is a region.

Follow the below steps to calculate the total salary.

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


    SUMIFS Example 2

  2. Apply the SUMIFS function in the table. Open SUMIFS function in excel.


    SUMIFS Example 3-1

  3. Select the sum range as F2 to F21.


    SUMIFS Example 3-2

  4. Select the B2 to B21 as the criteria range1.


    SUMIFS Example 3-3

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


    SUMIFS Example 3-4

  6. Second, Criteria Range2 will be C2 to C21.


    Example 3-5

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


    Example 3-6

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


    Example 3-7

  9. Now, simply Drag the formula to the remaining cells to have resulted in all the cells.


    Example 3-8

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

    Sumifs Formula

    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 the salary column, and the salary column range is F2:F21. We have locked this range (absolute referenceAbsolute ReferenceAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read more) 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 referenceAbsolute ReferenceAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read more) 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, the 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. 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.

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.

Example 3
Example 3-1

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

sumifs formula greater sign

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

  • 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 are 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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Comments

  1. bharat says

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

    • Dheeraj Vaidya says

      Thanks for your kind words!

Leave a Reply

Your email address will not be published. Required fields are marked *