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