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

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, …)

Where:

• sum_range is the column of numbers to add
• criteria_range1 is the column that contains the account names
• criteria1 is the specific account

Let’s give it a try. We write the following formula into C7:

=SUMIFS(Table1[Amount], Table1[Account], B7)

Where:

• 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.

Conclusion

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 Posted in , Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

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.

1. Lori on August 1, 2019 at 12:34 pm

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.

• Jeff Lenning on August 1, 2019 at 12:36 pm

I love VLOOKUP too … and 100% agree it is a critical, must-know function 🙂

2. Carl Walton on August 1, 2019 at 3:36 pm

• Jeff Lenning on August 1, 2019 at 4:34 pm

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,
Jeff

3. Carl Walton on August 8, 2019 at 7:35 am

Thanks for the clarification and use-case scenario