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 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • 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 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • 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
        • 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
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • 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
  • Sign Up
Home > Microsoft Excel > The SUMIFS Function in Excel – SUMIF Multiple Columns

The SUMIFS Function in Excel – SUMIF Multiple Columns

This SUMIFS Excel Function tutorial is suitable for users Excel 2013,2016, 2019 and Excel for Microsoft 365.

OBJECTIVE

Use the SUMIFS function in Excel to add numbers in a range of cells based on single or multiple criteria.

What is the Excel SUMIFS Function?

The SUMIFS Excel function is a much welcome enhancement to an old Excel favourite, SUMIF. Part of the Maths/Trig group of formulas, it can be used to add a range of numbers based on one or more pieces of criteria, or in simpler terms, SUMIFS works on multiple columns.

This makes it different from the Excel SUMIF function, which could only handle one piece of criteria. Essentially, we can SUMIF multiple columns with the help of the SUMIFS function.

Let’s take a step by step approach to understanding this mildly complex SUMIFs Excel function. Let’s break down this explanation with the help of these steps. 

  1. SUMIFS Excel Syntax
  2. Video Tutorial – SUMIFS Function in Excel
  3. Back To Basics – SUMIF Function in Excel
  4. Using Excel SUMIFS Function To Sum Values With Multiple Criteria
  5. SUMIFS Multiple Criteria Examples
    • SUMIFS 2 Criteria Example
    • SUMIFS 3 Criteria Example
    • SUMIFS Using Logical Operations Example
    • SUMIFS 5 Criteria Example

Let’s take a look at them in detail:

Related: 

Compare Two Lists Using VLOOKUP

Creating an Excel Dashboard in 5 Minutes

Using GETPIVOTDATA in Excel

SUMIFS Excel Syntax

One major difference between SUMIF and SUMIFS in terms of syntax is that when using Excel SUMIFS, the sum_range argument is specified first.

Once you have specified the range to be added, you can then specify the criteria range followed by the criteria. You can include up to 127 pairs of criteria.

=SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, … criteria_range_n, criteria_n] )

Video Tutorial – SUMIFS Function in Excel

SUMIFS Function in Excel

BACK TO BASICS – SUMIF function in Excel

Before we tackle SUMIFS function with multiple columns, let’s remind ourselves how Excel SUMIF function works.

Excel SUMIF can only handle one piece of criteria.

In the example below, I want to sum the Price for all Skirts. The Price is the sum_range, and the Skirt is the criteria.

SumIf example
Excel SUMIF Example

Note that the sum_range is entered last.

Sum_Range is entered last in the SUMIF function
Sum_Range is entered last in the SUMIF function

The range argument is the range of cells where I want to look for the criteria, A2:A19. The criteria argument is the criteria F2. The sum_range is the range I want to sum, D2:D19.

Note: I have my criteria set up as data validation drop-down lists to select different items, colors, and sizes. This also means I can use a cell reference as my criteria (F2). Alternatively, I could replace F2 in the formula with the name of my criteria. Additionally, I must enclose text entries in quote marks.

SUMIF Result
SUMIF Result

This is the result. As the list is reasonably short, I can do a visual check to ensure the calculation has worked correctly.

Also Read: 

The Excel FILTER Function

Ten Ways to Clean Data in Excel

Basic Forecasting in Excel: Recorded Webinar

Use SUMIF for single criterion
Use SUMIF for a single criterion

So that is how you sum values based on one piece of criteria.

Using Excel SUMIFS Function To Sum Values With Multiple Criteria

You have to use the SUMIFS function in Excel to sum values with multiple criteria, as the SUMIF function can handle only one criterion at a time. That is SUMIF multiple columns usage is not allowed in Excel.

On the other hand, the SUMIFS formula in Excel can work with multiple criteria simultaneously.

Now, I’ll show you how to use the SUMIFS function in Excel to calculate the sum based on multiple criteria.

SUMIFS Multiple Criteria Examples

SUMIFS – 2 Criteria

In the first example, I am going to sum using two pieces of criteria. I want to find out the total Price for all White Shirts.

When using SUMIFS Excel Function, the sum_range is specified first, D2:D19. The criteria_range1 is specified. Next, A2:A19 followed by criteria1, “Shirt.” Then, criteria_range2 is specified, B2:B19 followed by criteria2, “White.”

SUMIFS function in Excel
SUMIFS Function in Excel – Single Criteria Example

I could also construct this formula using cell references instead of text for my criteria.

SUMIFS Function in Excel - Cell References
SUMIFS Function in Excel – Cell References

SUMIFS – 3 Criteria

In this example, I am going to sum using three pieces of criteria.

I want to find out the total Price for all Blouses that are Blue and a size L.

SUMIFS function in Excel for 3 Criteria
SUMIFS function in Excel for 3 Criteria

This formula could also be written like this.

SUMIFS function in Excel - Using direct references
SUMIFS function in Excel – Using direct references

When using the SUMIFS function in Excel, the sum_range is specified first, D2:D19. The criteria_range1, A2:A19 is specified next. Followed by criteria 1, “Shirt.” Then, criteria_range2 is specified, B2:B19 followed by criteria 2, “White.”

SUMIFS – Using Logical Operations

In this example, I have added a ‘Revenue’ and a ‘Profit’ column.

This time, I want to use Excel SUMIFS to sum the total Revenue for all Blouses where the Profit is greater than $2.00. To accomplish this, I can use a logical operator as my criteria.

First, specify the sum_range, D2:D19. Next, specify criteria_range1, A2:A19 and the criteria1, G2. Then, specify criteria_range2 and the criteria2. Criteria2 is a logical test enclosed in quote marks.

I am only interested in summing the Revenue when the Profit is greater than (>) $2.00.

SUMIFS function with logical operator
SUMIFS function with logical operator

SUMIFS – Using 5 Criteria

In this example, I have summed the Revenue for all Blue Blouses, size L, and with a Profit of greater than $1.40.

SUMIFS function using logical operator for 5 Criteria

The answer is $23.00 as only one item matches all criteria.

SUMIFS function using logical operator for 5 Criteria

These are just a few simple examples of how to use SUMIFS in Excel to perform calculations with multiple criteria.

FAQs

Can we add multiple ranges of data using SUMIFS in Excel? 

Yes we can add multiple ranges of data using SUMIFs by adding together the results of two SUMIFS functions. 

What is the difference between SUMIF and SUMIFS?

SUMIF is used to add data based on a single criterion, while SUMIFS can be used to add data based on multiple criteria. 

How many arguments does the Sumifs function have in Excel?

The SUMIFS Excel function can handle upto 127 pairs of Criteria Range & Criteria Arguments. 

Suggested Reads: 

Compare Two Lists Using VLOOKUP

XLOOKUP Google Sheets – The Alternatives!

Excel vs Google Sheets

SUMIFS & other complex Excel functions made easy….

What do you think about the SUMIFS excel function? Isn’t it one of the smartest Excel functions out there? It enables you to SUMIF multiple columns without any hassle.

It is the easiest way to add rows of data based on a given condition. In this guide, we have covered several examples of the SUMIFS multiple criteria usage.

If you have any doubts regarding this or any other interesting suggestions for the SUMIFS function’s application, please let us know in the comment section below. We’re always glad to help you.  

If you are hungry for more rich quality Excel educational content check out our other articles on this blog. 

For more, free Excel training from Simon Sez IT. Take a look at our YouTube Channel or Resource Centre.

If you are ready to properly learn Excel, then take a look at the Simon Sez IT Excel Course Library.

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.

30 day vertical banner

Most Popular Posts

  • 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
  • How to Create Charts and Graphs in Microsoft Excel 2016

Similar Posts

How to Sharpen & Blur a Photo Using Photoshop Elements 15

Using Org Chart Themes, Layouts, and Arrangement in Visio 2010

How to Use Timed Slides for Your PowerPoint 2013 Presentation

How to Move Rows in Excel? 5 Easy Methods

The Ultimate Zoom Tutorial

How to Insert a Hyperlink in Excel? 3 Easy Ways

Course Categories

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

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

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