Creating a budget in Excel can be a painstaking process. Auditing what you spend on groceries, entertainment, rent, utilities, etc.. every month is a sobering exercise. However, if you know your way away some common functions in Excel in addition to Pivot Tables, the process will feel less like a burden.
Look Familiar?
Here we have our expenses for March, and we’re trying to figure out how much we spent on coffee. But imagine if we had 30 transactions over 4 months, and we wanted to know how much we spent across that entire period. Adding every amount up by hand wouldn’t scale.
SUMIFS to the rescue
In this case SUMIFS takes three arguments:
- The Sum range – What column do you want to add up? Column D has all of the amounts we spent, so we choose column D
- Criteria Range – Where are we looking to focus in on criteria? Since we’re looking for all of our coffee spending, we look in column C
- Criteria – Let’s tell Excel exactly what we’re looking for in column C: Coffee. In this case we’ve select F4 because it contains the word “Coffee”
Result: $12.25 spent on coffee in March. And it was damn good.
Want to search for the sum of a different category? Simply change the value in f4 and the formula changes with it.
We spent $103 in March at restaurants and bars.
