Article summary
Have you ever wanted to play “what if” games with a project backlog? Or a budgeting spreadsheet? If so, the SUMIF spreadsheet function is a handy little tool you should try.
So what does SUMIF do? I’ll defer to Google Docs’s definition for detail. In summary, it works just like SUM, but it only adds a subset of the items in a range.
A Simple Example: Purchasing Appliances
Let’s say you are about to purchase some new appliances. You’ve selected which appliances you want, but they would cost more than your $3,500 budget. SUMIF is helpful here–we can build a little spreadsheet, add our appliance costs, and see what the different combinations are going to cost.
SUMIF helps in selecting which new appliances will fit into our budget.
I’m using Numbers here, as I really like the checkbox cell format. Please review the above screenshot–you can see how I’ve made my little table with costs and a Purchase? column. Clicking the checkboxes will cause the Total to update automatically. This makes it super-easy to try out different combinations of appliances and see what will fit in the budget.
For reference, the syntax for this particular formula is =SUMIF(Purchase?,"=TRUE",Cost). Please see Google Docs or your favorite app’s help section for more detail.
SUMIF & Feature Prioritization Using Range Estimates
Using SUMIF in the appliance example is helpful, but I find it to even more useful when I’m trying to select which features will fit into a budget–in particular, when the features have been estimated using ABP/HP range estimates. Why? Because between the all the squaring, square-rooting, and other arithmetic, it’s non-trivial to glance at the list and see how the different costs add up.
SUMIF to decide which features are in and out.
Assume we have a project budget of 15. In the above screenshot, you can see how I’ve used Numbers’ checkbox feature to select which features are in and out and stay within budget†. If I were to click the checkbox for the “Fancy administration” feature, the total cost would go to 16, and we’d be over budget.
As you can see, with all of the different numbers and arithmetic, SUMIF makes it easier to include or remove features with the click of a checkbox.
Please see Google Docs or your favorite app’s help section for more help on SUMIF’s syntax.
Conclusion
I hope you find SUMIF as helpful as I do. A lot of examples you’ll find use it for complex filtering, but my favorite method is to use it with the straightforward checkboxes. It’s both visually appealing and quite satisfying!
† My deepest apologies to all administrators out there–sorry your interfaces always get de-prioritized. ;-)