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.
- SUMIFS Excel Syntax
- Video Tutorial – SUMIFS Function in Excel
- Back To Basics – SUMIF Function in Excel
- Using Excel SUMIFS Function To Sum Values With Multiple Criteria
- SUMIFS Multiple Criteria Examples
Let’s take a look at them in detail:
Related:
Compare Two Lists Using VLOOKUP
Creating an Excel Dashboard in 5 Minutes
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
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.
Note that the sum_range is entered last.
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.
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:
Ten Ways to Clean Data in Excel
Basic Forecasting in Excel: Recorded Webinar
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.”
I could also construct this formula using cell references instead of text for my criteria.
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.
This formula could also be written like this.
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 – 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.
The answer is $23.00 as only one item matches all 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!
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.
