Excel SUMIF Function
In this post, we’re going to dive into one of the most useful techniques in Excel … conditional summing. We will discuss the SUMIF function, what it does, what its arguments are, and how it compares to SUMIFS, VLOOKUP, and COUNTIF.
What the SUMIF Function Does
So, what is SUMIF in Excel? SUMIF is a function that allows you to do conditional summing. What is conditional summing? It is adding up a range of values based on a specific criteria. For example, let’s say you have a list of sales figures for different products and you want to add up the sales for a specific product. That’s where conditional summing comes in handy.
If you’re new to Excel, SUMIF might seem daunting at first. But don’t worry, it’s actually not too bad. There are three arguments:
=SUMIF(range, criteria, sum_range)
- range is the criteria range
- criteria is the criteria value
- sum_range is the column of numbers you want to add
Here is the narrative I think about as I write the formula, which helps me remember the order of the arguments:
When ‘this’ is equal to ‘this’ add ‘this’
Let’s take a look at how to use SUMIF in Excel with a step-by-step example.
You have a table full of transactions named Table1:
You want to write a formula that will summarize the transactions in a report that looks like this:
In cell C7, we could use SUMIF in a formula like this:
=SUMIF(Table1[Account], B7, Table1[Amount])
We can copy/paste that formula down, and bam:
Sweet! Now, let’s talk through some common questions about SUMIF:
What is the difference between SUMIF and SUMIFS?
SUMIF allows you to use one criteria or condition, while SUMIFS allows you to use one or more conditions. Also, the order of the arguments is different. With SUMIF, the sum_range is the last argument. With SUMIFS, it is the first argument:
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
Then you can tack on additional pairs of criteria arguments as desired (up to about 100 pairs of criteria arguments).
What is the difference between SUMIF and VLOOKUP?
While both functions can be used to return values from a table based on a specific criteria, VLOOKUP finds the first matching value and stops. That is, it returns only the first matching value from the table. On the other hand, SUMIF returns the SUM of all matching rows.
What is the difference between SUMIF and COUNTIF?
While SUMIF adds up the values from a column based on a condition specified in the criteria arguments, COUNTIF simply counts the number of rows that meet the condition. It doesn’t have a sum_range argument:
So, SUMIF is used to add a numeric column whereas COUNTIF is used to count the number of matching rows.
How do I do a SUMIF with multiple criteria in Excel?
To do a conditional sum with multiple conditions, use the SUMIFS function rather than the SUMIF function:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
You can add additional pairs of criteria arguments as needed.
Can I add 2 or 3 or more conditions in SUMIF?
Since SUMIF supports only a single condition, you’ll want to use SUMIFS to if you have multiple conditions.
Can you add 2 SUMIFS together?
Yes, you can add the results of two SUMIFS functions together to get a total. It would look like this:
=SUMIFS(sum_range,criteria_range1,criteria1) + SUMIFS(sum_range,criteria_range1,criteria1)
This can be handy for applying OR logic, where the criteria_range can be one value or another to be included in the total.
Conditional summing is a very handy skill to have, and can be applied in many different workbooks. In practice, I tend to use SUMIFS most of the time because it can also handle a single condition. That way, I don’t have to remember the argument order differences between SUMIF and SUMIFS. But, either way, both of these functions can be very helfpul.
If you have any additional tips or suggestions, or if you have any questions, please let me know by posting a comment below … thanks!
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.