EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel SUMIF with Multiple Criteria
Secondary Sidebar
Excel Functions
  • Logical Functions in Excel
    • Excel IF AND Function
    • IFERROR Formula in Excel
    • SUMIF with Multiple Criteria
    • Excel SUMIFS with Dates
    • FALSE Function in Excel
    • Excel IF Function
    • Excel Logical Test
    • Excel XOR Function
    • Logical Functions Excel
    • IFERROR Excel Function
    • TRUE Function in Excel
    • OR Function in Excel
    • NOT in Excel
    • AND Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL ADVANCED Certification
  • Excel for HR Training

SUMIF with Multiple Criteria

By Ashwani JaiswalAshwani Jaiswal

SUMIF With Multiple Criteria

SUMIF with Multiple Criteria (Table of Contents)

  • Excel SUMIF Function with Multiple Criteria
  • How to Use SUMIF Function with Multiple Criteria in Excel?

Excel SUMIF Function with Multiple Criteria

SUMIF in excel is used for calculating the total of any specified criteria and range, unlike the sum function, which is a single shot that calculates the total of the whole range without any specific criteria. We can use SUMIF for adding or subtracting different criteria ranges.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

It can be accessed from Insert Function from the Math & Trig category.

Syntax for SUMIF

For better understanding, we have shown the syntax below.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,858 ratings)

SUMIF Formula

Arguments of SUMIF Function:

  • Range – This first range is the range for which we need to calculate the sum.
  • Criteria – It is for fixing the criteria for what part of the range we need to calculate the sum.
  • Sum_Range – It is that range which needs to get summed for defined criteria.

How to Use Excel SUMIF Function with Multiple Criteria?

Let’s understand how to use Multiple Criteria by using some examples in Excel.

You can download this SUMIF Function with Multiple Criteria Excel Template here – SUMIF Function with Multiple Criteria Excel Template

SUMIF with Multiple Criteria – Example #1

We got the sales data for some product class. As we can see below, the data which we have has the owner’s names and quantity sold by them for the respective product class.

SUMIF With Multiple Criteria Example 1-1

Now we will apply multiple criteria in the simple SUMIF function.

Considering the multiple criteria for calculating the sum of the above-shown data, we need to apply SUMIF so that we can get the sum of more than 1 owner’s quantity sold data. Here we will calculate the total quantities sold by Ben and Jenny together.

For this, go to the cell where we need to see the output and type the “=” (Equal) sign. This will enable all the inbuilt functions of excel. Now search and select the SUMIF function from the search list.

For calculating the sum of quantities sold by Ben, select the Owner’s name as in range, criteria as BEN, and sum range as complete Quantity Sold column C and press enter.

SUMIF With Multiple Criteria Example 1-2

We will get the sum of Ben’s sold quantity as 24383.

SUMIF With Multiple Criteria Example 1-3

To add another criterion in this syntax, we will add the quantity sold by Jenny with the help of the “+” sign as shown below.

SUMIF With Multiple Criteria Example 1-4

Now press enter to get the total sum.

SUMIF With Multiple Criteria Example 1-5

As we can see in the above screenshot, the quantity sold by Ben and Jenny together is coming as 35607. We can cross-check the sum separately as well to match the count with the SUMIF function.

SUMIF with Multiple Criteria – Example #2

There is another way to apply SUMIF criteria. For this, go to the cell where we need to see the output and click on the Insert function besides the formula bar as shown below.

SUMIF With Multiple Criteria Example 2-1

We will get the Insert Function window. Now from there, select the function SUMIF from Or select a category of ALL as shown below.

SUMIF With Multiple Criteria Example 2-2

We can see the suggested syntax at the bottom of the window. And click on, Ok.

Now in the functional argument box, select the A2 to A9, Criteria as Ben and sum range as C2 to C9 and click on Ok. This will frame the first half of the multiple criteria syntax.

SUMIF With Multiple Criteria Example 2-3

Now insert plus sign (+) as shown below.

SUMIF With Multiple Criteria Example 2-4

And click on Insert Function and search for SUMIF and click on Ok, as shown below.

 Example 2-5

And now, in the second half of the arguments of SUMIF, multiple criteria select the range and sum range same as selected for the first half of the syntax and enter criteria as Jenny. Once done, click on, Ok.

  Example 2-6

If we see the complete syntax, it will look like as shown below.

 Example 2-7

Now press enter to see the final output.

Example 2-8

As we can see in the above screenshot, the output of SUMIF with multiple criteria is 35607 same as we got in example 1.

SUMIF with Multiple Criteria – Example #3

There is another method of using SUMIF with multiple criteria. Here we will use the same data which we have seen in example-1. We will also86+ see the calculated value of quantity sold by Ben and Jenny so that we can compare the result obtained from this method with the result got in example-1.

We will use Sum with SUMIF with multiple criteria of Ben and Jenny’s quantity sold.

Now go to the cell where we need to see the output of SUMIF and type “=” sign and search and select Sum function first.

SUMIF With Multiple Criteria Example 3-1

Now search and select the SUMIF function as shown below.

Example 3-2

Now select the range as A2 to A9, set the criteria as Ben and Jenny in inverted commas under curly brackets { } and select sum range as C2 to C9, as shown below.

Example 3-3

Note: Adding Criteria in curly brackets are necessary. This allows the multiple criteria in the formula. Now press enter to see the result as shown below.

Example 3-4

As we can see, the calculated result in example-1 and example-2 are equal. Which says our used multiple criteria for this example is also correct.

Pros of SUMIF with Multiple Criteria

  • It is good to use multiple criteria with SUMIFto get the result quickly.
  • The syntax may look complex, but it is better to use to once instead of using it in different cells and later adding up then.
  • We can insert as many criteria as required.

Cons of SUMIF with Multiple Criteria

  • As the syntax for multiple criteria, SUMIF has more than 1 criteria, so sometimes it becomes complex to rectify the error.

Things to Remember

  • Use curly brackets {} if you are going to use SUMIF with multiple criteria, as shown in example-3. And in that curly brackets, enter the content manually instead of framing it with cell selection. Curly brackets support only text entered in it.
  • Syntax of example 3, i.e. calculating sum with multiple criteria, is small and easy to use, so it is always recommended.

Recommended Articles

This is a guide to SUMIF Function with Multiple Criteria in Excel. Here we discuss how to use SUMIF Function with Multiple Criteria along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. SUM Function in Excel
  2. SUMIF in Excel
  3. COUNTIF with Multiple Criteria
  4. SUMIF Formula in Excel
Popular Course in this category
MS Excel Training Bundle
  13 Online Courses |  100+ Hours |  Verifiable Certificates |  Lifetime Validity
4.5
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel for HR Training (8 Courses, 10+ Projects)4.8
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download SUMIF Function with Multiple Criteria Excel Template

EDUCBA

Download SUMIF Function with Multiple Criteria Excel Template

EDUCBA

डाउनलोड SUMIF Function with Multiple Criteria Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more