Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > Excel SUMIF vs SUMIFS – The Complete Guide

Excel SUMIF vs SUMIFS – The Complete Guide

This article deals with the Excel SUMIF and SUMIFS functions. You’ll learn how to use them along with their key differences.

In this article, I’ll cover:

Table Of Contents
  1. Difference Between Excel SUMIF and  Excel SUMIFS Functions
  2. Video Tutorial: Excel SUMIF and other Logical Functions
  3. How to use SUMIF and SUMIFS ?
  4. FAQs
  5. Closing Thoughts on SUMIFS vs SUMIF Functions


Difference Between Excel SUMIF and  Excel SUMIFS Functions

In your Excel journey, you’ll probably come across situations where you need to find the SUM for certain values only. 

Located in the Math and Trig category, SUMIF and SUMIFS are essential MS Excel functions. Both are logical SUM functions that find the SUM of a given range based on specified conditions. The only difference between Excel SUMIFS & SUMIF functions is that SUMIFs can check for multiple criteria at once, while SUMIF can check for one criterion at a time.

Related: 

Sumif With Multiple Columns – The Sumifs Function In Excel

Advanced Formulas In Excel – 1 Hour Recorded Webinar

Importing And Cleaning Data In Excel

The SUMIF formula returns the sum of cells based on one criterion (a result that matches one condition).

Whereas, the SUMIFS function returns the sum of cells that meet multiple criteria.

Let’s say, you need to find the sum of sales revenue in a particular region, you may use SUMIF. On the other hand, if you need to find the sum of sales revenue for a particular region & a particular product type, you should then use the SUMIFS function

The criteria mentioned in both functions can be dates, numbers, and text. The function supports logical operators like >, <, <>, =, and wildcards „*,?“ for partial matching.

Video Tutorial: Excel SUMIF and other Logical Functions

For a demonstration of how to perform Excel SUMIFS, take a look at this hour-long class that we held on Logical Functions in Excel:

How to use SUMIF and SUMIFS ?

Let’s break down the explanation into simpler steps for better clarity.

  1. Excel SUMIF function
  2. SUMIF function Example 1
  3. SUMIF function Example 2
  4. Excel SUMIFS function
  5. SUMIFS function Example 1

Excel SUMIF Function

Probably the most used function in MS Excel is the SUM function. Using the SUM function, you get the total SUM of the selected cells as a result.

But sometimes, we don’t need to SUM all of the cells in a row or column. Occasionally, we need a specific condition to be met in order to sum that cell. That is when we use the SUMIF formula.

The SUMIF function can be found in the Mathematical and Trig category.

The syntax for the SUMIF function is =SUMIF (range, criteria, [sum_range])

Example 1

Let’s look at an SUMIF example of values of different properties that were sold by an estate agent and the commissions the estate agent has gotten.

Excel SUMIFS vs Excel SUMIF - Get your data ready
Get your data ready

Let’s say our boss gives us a couple of questions like:

  • What is the sum of the commissions for property values over $160,000?
  • What is the sum of the property values over $160,000?
  • What is the sum of the commissions for property values equal to $300,000?

It would be straightforward to find the answers to these questions manually, but what if you had hundreds and thousands of rows of data?

Then you’d have to use the SUMIF excel function.

 What is the sum of the commissions for property values over $160,000?

SUMIF Function can handle only one logical criterion
The SUMIF Function can handle only one logical criterion

The first argument of the syntax is the range. We are checking all of the property values. The condition is that they are higher than 160000. If they are higher than 160000, sum up the commissions for those that meet the criteria.

=SUMIF(A2:A5;”>160000″;B2:B5)

The function result of $63000 can be easily manually checked. We can see that properties A3, A4, and A5 are all valued higher than 160000. That means we sum up their respective commissions at B3, B4, and B5.

14000+21000+28000= 63000

We used the SUMIF function correctly and got the correct result.

What is the sum of the property values over $160,000?

SUMIF function to check sum of property values over $160,000
SUMIF function to check the sum of property values over $160,000

You can see straight away that only two arguments were used in this function. The range and the criteria. Let’s look at the syntax again.

=SUMIF (range, criteria, [sum_range])

When the [sum_range] is the same as the range, that’s when you don’t need to enter it. You can leave it blank.

SUMIF function with SUM_RANGE
SUMIF function with SUM_RANGE

You can, of course, use the [sum_range], the result will be correct.

So what did we do in this example? It’s very simple. We checked if the property value is higher than $160000, and if it was, we summed it up.

=SUMIF(A2:A5;”>160000″)

Property values A3, A4, and A5, are higher than 160000, and when summed up, they give the correct 900000 value.

200000+300000+400000=900000

The function was used correctly, and the result was correct, as well.

What is the sum of the commissions for property values equal to $300,000?

SUMIF Function to calculate property values equal to $300,000
SUMIF Function to calculate property values equal to $300,000

We are back to summing up commissions again. Now the condition is that the property value must be equal to $300000. There is only one property value that meets the condition, and the commission is 21000.

That means we used the Excel SUMIF function correctly and have gotten the right result.

Example 2

In this example, we’ll take a look at what if the condition isn’t a number. How does MS Excel handle words as conditions?

Our boss has given us these questions:

  • What is the sum of the sales of all the foods in the “Fruits” category?
  • What is the sum of the sales of all the foods in the “Vegetables” category?
  • What is the sum of the sales of all foods that end in “es” (Tomatoes, Oranges, and Apples)?
  • What is the sum of the sales of all foods that do not have a category specified?

What is the sum of the sales of all the foods in the “Fruits” category?

The Sum of the sales of all the foods in the “Fruits” category using the SUMIFS function
The Sum of the sales of all the foods in the “Fruits” category using the SUMIFS function

Let’s explain what we did to get the formula that can be seen in the picture

=SUMIF(A2:A7;A4;C2:C7)

For the range, we took everything in the category row from A2 to A7.

The criterion is that the food is fruit. Since fruit is mentioned in A4, we simply clicked on it. We could have also entered it manually as „Fruit“.

The [sum_range] is everything in the Sales row, from C2 to C7

The result is $2000, which is the correct result as oranges account for $800 in sales and apples are $1200. That is a total of $2000.

What is the sum of the sales of all the foods in the “Vegetables” category?

The Sum of the sales of all the foods in the “Vegetables” category using the SUMIFS function

The formula =SUMIF(A2:A7;”Vegetables”;C2:C7) shows that we used a different approach to the criteria argument this time.

The range stayed the same, everything from A2 to A7.

The criterion was entered manually „Vegetables. “

The [sum_range] is, of course, all of the Sales numbers from C2 to C7.

We got the result of 12000. We know that is correct because we can manually add Tomatoes, Celery, and Carrots, and we get the sum of 12000.

Also Read: 

Macros And Basic Vba In Excel – Recorded Webinar

Excel Vs Google Sheets

The Excel Solver Add-in: Five Minute Tutorial

What is the sum of the sales of all foods that end in “es” (Tomatoes, Oranges, and Apples)?

The Sum of the sales of all foods that end in “es” (Tomatoes, Oranges, and Apples)
The Sum of the sales of all foods that end in “es” (Tomatoes, Oranges, and Apples)

What can we see from the =SUMIF(B2:B7;”*es”;C2:C7) formula?

The range is the B row this time, from B2 to B7. We are checking all of the foods, not the category.

The criterion is that the food name ends with „es“. To do that we use „*“ in front of es. The „*“ represents any letters before „es“.

Just a quick example, if we wanted to sum up the values of all food that start with a C, we would enter „C*“ as the criteria.

And finally, for the [sum_range], we use everything in the Sales row from C2 to C7.

By manually adding Tomatoes, Oranges, and Apples, we know that the result of $4300 is correct and that we have used the SUMIF function correctly.

What is the sum of the sales of all foods that do not have a category specified?

The Sum of the sales of all foods that do not have a category specified

=SUMIF(A2:A7;””;C2:C7)

After taking a look at the formula we used to get the correct result, only the criteria stand out.

The quotation marks „“ have no text between them. That is because we are checking the category range for no category. That means no text.

You have to write the quotation marks manually, and simply clicking on an empty cell won’t work as it counts as a zero.

We can see that we used the SUMIF function correctly again, and the result is $400 as butter is the only food without a category.

Excel SUMIFS function

The Excel SUMIFS function sums up cells that meet multiple criteria. For example, we can use Excel SUMIFS to sum the number of retailers who (1) reside in a specific zip code and (2) whose profits exceed a particular amount.

Just like Excel SUMIF, it supports logical operators (>,<,<>,?) and wildcards (*,?).

Example 1

If we wanted to find out the total amount of money for the red color, we’d simply use the SUMIF function because that’s only one criterion.

But what if we wanted to find the sum of all Red in the state of Texas.

The Sum of all Red in the state of Texas
The Sum of all Red in the state of Texas

Using the SUMIFS function with the criteria Red and Tx got us the result of $54.

$18+$36=$54

We used the SUMIFS function correctly. But what did we do?

Let’s take a closer look at the formula

=SUMIFS(E2:E8;B2:B8;B2;C2:C8;C2)

SUMIFS function arguments
SUMIFS function arguments

The Sum_range is everything in the Total($) row, which means E2 to E8.

The first criteria range is everything in the Color row, which is B2 to B8.

The first criterion is that the color is Red, so we simply clicked on B2, or we could have manually entered „Red“.

The second criteria range is everything in the State row which is C2 to C8.

And the second criterion is TX, so we clicked on C2, or we could have manually entered „TX“.

We got the result of $54, which is the correct result.

Suggested Reads: 

Dynamic Array Functions In Excel (Xlookup, Filter, Unique, Xmatch): Webinar Recording

Creating An Excel Dashboard In 5 Minutes

Dynamic Arrays In Excel: Eight, Must-know Formulas

FAQs

Is SUMIFS better than Vlookup?

Yes, Excel SUMIFS is definitely better than VLOOKUP when it comes to finding the SUM of a range based on logical criteria. Though VLOOKUP  is a standalone powerful function, using it for finding conditional sums is a roundabout way of doing it. 

Can you do a SUMIF with 2 criteria?

No, you can’t use the SUMIF function for 2 criteria. For more than one criteria, you need to use the Excel SUMIFs function.

How many criteria can be used in the SUMIFS function?

At a time, up to 127 criteria can be used in a single SUMIFS function. 


Closing Thoughts on SUMIFS vs SUMIF Functions

We are finally at the end of this SUMIFS & SUMIF guide. We hope you now have a lot of clarity about both the SUMIFS & SUMIF functions; the differences between them and their use cases. 

If you have any questions & doubts about Excel SUMIFS & SUMIF functions or any other Excel feature, feel free to ask them in the comments below. 

If you need more high-quality Excel guides, please check out our free Excel resources centre. We at Simonsezit are committed to simplifying learning Excel for you. 
Click here for advance Excel courses with in-depth training modules

Microsoft Office skills
Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

Monday.com Dependencies – Tips and Types Explained [2022]

The 3 Essential Scrum Artifacts You Must Know in 2022

How to Use the Selection Brush in Photoshop Elements 15

Exporting to Microsoft Excel in Project 2010

Online Help and Contextual Help in Microsoft Excel 2016

Get the Most Out of Microsoft Teams (1 Hour Recorded Webinar)

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)