SUMIF with Multiple Columns—The Excel SUMIFS Function
This Excel SUMIFS Function tutorial is suitable for users of 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.
SUMIFS Excel Function Made Easy
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, the SUMIFS function works on multiple criteria at once.
Related:
Compare Two Lists Using VLOOKUP
Creating an Excel Dashboard in 5 Minutes
This makes it different from the Excel SUMIF function, which could only handle one piece of criteria.
Let’s take a step by step approach to understand this mildly complex SUMIFs Excel function. Let’s break down this explanation with the help of these steps.
Let’s take a look at them in detail. I have included many Excel SUMIFS function examples for your clarity.
Syntax
One major difference between the SUMIF and SUMIFS function 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 in Excel
BACK TO BASICS – SUMIF function in Excel
Before we tackle the SUMIFS function, let’s remind ourselves how the 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, colours, 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.
Excel SUMIFS Function Examples
Now, I’ll show you how to use the SUMIFS function to find SUM based on multiple criteria.
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.”
To put it simply, just add additional ranges and their associated criteria in the SUMIFS function to make it work with multiple criteria.
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 in any of the criteria ranges.
First, specify the Sum Range 1, D2:D19. Next, specify Criteria Range 1, A2:A19 and Criteria 1, G2. Then, specify Criteria Range 2 and Criteria 2. Criteria 2 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 SUM Range is D2:D19, followed by the criteria ranges.
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 up to 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 is the easiest way to add rows of data based on a given condition.
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.
