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:
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.
- Excel SUMIF function
- SUMIF function Example 1
- SUMIF function Example 2
- Excel SUMIFS function
- 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.
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?
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?
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.
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?
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?
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 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
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)?
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?
=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.
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)
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
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.