WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » SUMIFS With Multiple Criteria

SUMIFS With Multiple Criteria

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

What is SUMIFS with Multiple Criteria?

Summing up values in excel based on conditions is the kind of logical calculation we do to get the sum based on condition. To perform these logic-based calculations, we have a variety of functions in excel. When you want to sum values based on more than one criterion, then we need to use the SUMIFS formula in excel. In this article, we will guide you on how to use the SUMIFS formula with multiple criteria.

SUMIFS-With-Multiple-Criteria

SUMIFS Formula in Excel

SUMIFS is the improved version formula of SUMIF function in excel. SUMIFS allows us to match multiple criteria to sum any range of values. 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.

So, when the criteria to arrive, the result is single, we can use SUMIF, and in case of more than one criteria, we can use SUMIFS function.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Below is the syntax of the SUMIFS formula.

Sumifs syntax

  • Sum Range: This is simply the range of cells that we need to sum.
  • Criteria Range 1: To Sum Range what is the criteria range.
  • Criteria 1: From the Criteria Range 1 what is the one particular value we need to sum?
  • Criteria Range 2: To Sum Range what is the second criteria range.
  • Criteria 2: From the Criteria Range 2 what is the one particular value we need to sum?

Like this, we can give 127 criteria ranges to sum one particular value.

How to Use SUMIFS with Multiple Criteria?

Below are examples of how to use the SUMIFS formula with multiple criteria.

You can download this SUMIFS With Multiple Criteria Excel Template here – SUMIFS With Multiple Criteria Excel Template

Example #1

For example, look at the below sales data.

Excel Data

From the above table, we need to find “what is the total sales for the city “Florida” and for the month of “Aug.”

Multiple Criterias

  • Open SUMIFS function in the I2 cell.

Example 1.1

  • The first argument of the SUMIFS function is Sum Range, i.e., what is the column we need to sum, so in this case, we need to sum the “Sales” column, so select the range of values from E2 to E16.

Example 1.2

  • The second argument is Criteria Range 1, i.e., based on what criteria we need to sum the “Sales” column. In this case, our first criteria are to sum values based on the “State” column, so for this argument, select A2 to A16 cells.

Example 1.3

  • After mentioning the Criteria Range 1, we need to mention what is the Criteria 1 value from the selected Criteria Range 1. In this range, we need the sum value of the state “Florida,” so we have this state value in G2 cell, give the cell reference.

Example 1.4

  • Now we need to select the second Criteria Range 2 so our second criteria range is to sum value is “Month,” so select the cells from D2 to D16.

Example 1.5

  • After mentioning the Criteria Range 2, we need to mention what is the Criteria 2 value from the selected Criteria Range 2. In this range, we need the sum value of the month “Aug,” so we have this state value in H2 cell, give the cell reference.

Example 1.6

  • Ok, we are done with supplying all the criteria. Close the bracket and hit enter to get the result.

Sumifs with Multiple Criteria Result

So for the city “Florida” and for the month, “Aug” total sales is $1,447. So SUMIFS function first looks for the city “Florida,” and in this city, it looks for the month of “Aug,” and whichever the rows match these two criteria are summed up.

Example #2

Now for the same data, we will see how to use more criteria. For example, for the same state “Florida” and for the month of “Aug” and for the sales rep “Peter,” we need to find the sales value.

  • For the old formula, we need to add one more criterion, i.e., the “Sales Rep” criteria of “Peter.”

Sumifs with Multiple Criteria - Example 2.1

  • For the Criteria Range 3, choose the “Sales Rep” cell values.

Sumifs with Multiple Criteria - Example 2.2

  • After selecting the Criteria Range 3 column, we need to mention the Criteria 3, i.e., we need only the sum of the “Sales Rep” “Peter,” so give cell reference as I6 cell.

Sumifs with Multiple Criteria - Example 2.3

  • Ok, third criteria are also supplied, so close the bracket and hit the enter key to get the result.

Multiple Criteria - Example 2.4

The only one-row item matches the criteria of “State = Florida,” “Month = Aug,” and “Sales Rep = Peter,” i.e., row number 5 (colored with green).

Things to Remember

  • SUMIFS formula can match 127 criteria’s.
  • Cell reference length should be the same for all the parameters of the formula.

Recommended Articles

This has been a guide to SUMIFS with Multiple Criteria. Here we discuss how to sum values based on more than one criterion using SUMIFS Formula in Excel. You can learn about excel from the following articles –

  • Excel Match Multiple Criteria Examples
  • Examples to Use Excel Convert Function
  • Using SUMIFS with Dates
  • Using Sumif Text in Excel
8 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download SUMIFS With Multiple Criteria Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More