EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Maths Function in Excel SUMIF with Multiple Criteria
 

SUMIF with Multiple Criteria

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated May 9, 2023

SUMIF With Multiple Criteria

 

 

SUMIF with Multiple Criteria (Table of Contents)

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

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

Syntax for SUMIF

For better understanding, we have shown the syntax below.

SUMIF Formula

Arguments of SUMIF Function:

  • Range – This first range is the range 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 – That range needs to be summed for defined criteria.

How to Use Excel SUMIF Function with Multiple Criteria?

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

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

Examples of SUMIF with Multiple Criteria

Example #1

We got the sales data for some product class. As we can see below, the data we have has the owners’ names and the 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 to 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 to 35607. We can cross-check the sum separately to match the count with the SUMIF function.

 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. Select the function SUMIF or an ALL category from there, 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.

In the functional argument box, select the A2 to A9, Criteria as Ben, and sum range from C2 to C9 and click 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, as we got in example 1.

 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 to compare the result obtained from this method with the result 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 the “=” sign, and search and select the Sum function first.

SUMIF With Multiple Criteria Example 3-1

Now search and select the SUMIF function as shown below.

Example 3-2

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

Example 3-3

Note: Adding Criteria in curly brackets is 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 use of multiple criteria for this example is also correct.

Pros and Cons of SUMIF with Multiple Criteria

Pros

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

Cons

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

Things to Remember

  • Use curly brackets {} if you 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 them.
  • 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. We discuss using SUMIF Function with Multiple Criteria, practical examples, and a downloadable Excel template here. 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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

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

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download SUMIF Function with Multiple Criteria Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download SUMIF Function with Multiple Criteria Excel Template

EDUCBA

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

🚀 Limited Time Offer! - 🎁 ENROLL NOW