Battle of Heavyweights: SUMIFS
This is the second post in the Battle of Heavyweights series, where we are comparing VLOOKUP to SUMIFS in order to understand their key differences. Knowing these key differences will help us determine which to use in a given workbook. In the previous post, we were introduced to VLOOKUP. Now, it is time to meet the challenger: SUMIFS.
SUMIFS is a conditional summing function. That means it adds stuff up …. like SUM; but it is conditional … meaning it only includes selected rows.
Let’s get to an example.
Let’s say we need to compute the totals for a few selected accounts. We enter the account names into some cells and leave a column for Amount, like this:
To figure out the amount values, we need to look through our transaction table and add up various rows. Here is a look at the the first several rows of the transactions table (Table1):
To complete our report, we need to add up the transactions for each account. This is where SUMIFS will help.
Let’s think about the information SUMIFS will need to compute the total for each account. It will need to know which column in the data table contains the amount values. However, we don’t want the result to include all of the rows in that column. We only want to include some of the rows … that is, the rows that contain transactions for a specific account. So, it needs to know which column contains the account and which account to aggregate. Fortunately, the SUMIFS arguments are set up in exactly this order:
=SUMIFS(sum_range, criteria_range1, criteria1, …)
- sum_range is the column of numbers to add
- criteria_range1 is the column that contains the account names
- criteria1 is the specific account
- … additional criteria pairs
Let’s give it a try. We write the following formula into C7:
=SUMIFS(Table1[Amount], Table1[Account], B7)
- Table1[Amount] is the column of numbers to add
- Table1[Account] is the column of account names
- B7 is the cell with our first account name
We hit Enter, and bam:
We push the formula down, and bam:
Yay … it works!
Here are a few notes:
- SUMIFS handles multiple conditions, and each condition is defined with a pair of arguments (note: SUMIF handles a single condition; SUMIFS handles multiple conditions).
- SUMIFS uses “and” logic, meaning all conditions must be true for that row to be included in the sum. If you need to use “or” logic instead, check out this post.
- We could prepare the specific report used in this post with a PivotTable. However, SUMIFS enables us to present summary data in ways that PivotTables aren’t designed to, for example, in financial statements. In fact, check out this post if you want to create a balance sheet with SUMIFS.
So, to recap, SUMIFS is a conditional summing function. It adds a column of numbers, but, only includes certain rows. It only includes the rows that meet the specified condition.
Now that we have met our two competitors VLOOKUP and SUMIFS … the battle begins in the next post!
Sample file: IntroducingSUMIFS.xlsx
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.
While SUMIFS is indeed useful, I use VLOOKUP more than all other functions combined! I literally use it every workday. I preach to anyone who’ll listen that if you use Excel, learn VLOOKUP.
I love VLOOKUP too … and 100% agree it is a critical, must-know function 🙂
What are the advantages/disadvantages of Sumifs compared to a PivotTable
Hi Carl! Generally, if the report I’m trying to build can be built with a PT, I go with a PT. But, when the report I’m trying to build doesn’t fit within the structure offered by PTs, then I go with SUMIFS and create a formula-based report. An example could be a complex financial statement or a balance sheet. Hope it helps!
Thanks for the clarification and use-case scenario