Another Overlooked Function More People Should Take a Look at
One Excel function that I’m surprised more people don’t use more often is the SUMIF. It’s a fairly simple function that even people working on uncomplicated spreadsheets may often have an occasion to use.
The purpose of the SUMIF is to allow you to create totals without sorting your main data table. I used to work with data tables at the retail giant Cabela’s that ran well over a hundred thousand rows. Every time I ran a fresh data dump, I wanted some quick diagnostic totals by category to quickly gauge how things had changed.
Sure, I could create a pivot table or use the SUBTOTAL function, but there can be problems with both of those solutions. First, the rows in a pivot table may move up or down depending on what data is present. This can ruin formulas elsewhere in the spreadsheet. As for the SUBTOTAL function you might use to sort your data in the Data menu option, that will work, but it has to be done fresh every time. A fresh data dump will erase all of your previous subtotals.
Using the SUMIF function allows you to anchor values that won’t move up or down like in a pivot table. It will also update itself automatically whenever you import fresh data.
For instance, let’s say that your manufacturing plant had a list of inventory on hand in one big table. If you wanted the total inventory by product line, you don’t want to have to sort the table by product line and manually add SUM formulas for each section. Besides being time consuming, the sum ranges would change every time the list was updated, forcing you to redo all your formulas.
Setting up SUMIF formulas would be a one-time job that you wouldn’t have to update every time the main table was refreshed. It’s not a complicated formula as far as Excel goes. There are three main parts and the graphic below describes each one.
Sure, I could create a pivot table or use the SUBTOTAL function, but there can be problems with both of those solutions. First, the rows in a pivot table may move up or down depending on what data is present. This can ruin formulas elsewhere in the spreadsheet. As for the SUBTOTAL function you might use to sort your data in the Data menu option, that will work, but it has to be done fresh every time. A fresh data dump will erase all of your previous subtotals.
Using the SUMIF function allows you to anchor values that won’t move up or down like in a pivot table. It will also update itself automatically whenever you import fresh data.
For instance, let’s say that your manufacturing plant had a list of inventory on hand in one big table. If you wanted the total inventory by product line, you don’t want to have to sort the table by product line and manually add SUM formulas for each section. Besides being time consuming, the sum ranges would change every time the list was updated, forcing you to redo all your formulas.
Setting up SUMIF formulas would be a one-time job that you wouldn’t have to update every time the main table was refreshed. It’s not a complicated formula as far as Excel goes. There are three main parts and the graphic below describes each one.
To show you how this would look in a simple example. I created a scenario where there are twenty-eight inventory item numbers in a table but only three product lines. If I want to see the total inventory by product line, I would do the following:
Each week or month I could copy fresh data into my green table and the product line totals up above would automatically update themselves. Notice that even though I have only 28 item numbers, my range goes down to row 939. This allows for future growth. If I set my range only down to row 43 and we add 10 new item numbers, those new items would get left off of my totals if I didn’t remember to update the range. So it’s always good to leave some room for growth.
In my summary table, I have three product lines. These totals can be linked as source data in other spreadsheets if need be. What would happen if I was using a pivot table and there was no Enerflex inventory in a week? The pivot table would move the Primo product line up one row, where Enerflex used to be. This could be a potential problem if the product line totals feed into other formulas.
This is just one specific example of how SUMIF can allow you to automate some recurring reporting that you might do. However, anyone who works with large data tables on a regular basis would find many uses in the course of their daily work. If there’s one thing that data workers do a lot of, it is summarizing totals by some kind of criteria.
Related: Make Your Vlookup DOUBLE Variable
Now available in iBooks —> The Tesla Bubble
In my summary table, I have three product lines. These totals can be linked as source data in other spreadsheets if need be. What would happen if I was using a pivot table and there was no Enerflex inventory in a week? The pivot table would move the Primo product line up one row, where Enerflex used to be. This could be a potential problem if the product line totals feed into other formulas.
This is just one specific example of how SUMIF can allow you to automate some recurring reporting that you might do. However, anyone who works with large data tables on a regular basis would find many uses in the course of their daily work. If there’s one thing that data workers do a lot of, it is summarizing totals by some kind of criteria.
Related: Make Your Vlookup DOUBLE Variable
Now available in iBooks —> The Tesla Bubble
RSS Feed