One of the tricks I use in my monthly budget spreadsheets is the SUMIF function. This is a powerful tool that helps me to easily summarize the financial information I record in the transaction section of my budget spreadsheet into clear categories for me to analyze and assess. I am able to track the monthly spending of all my budget categories with zero hassel – and it even helps me track how much my wife and I charge on our credit card each moth. The SUMIF function is very straightforward to use and it only takes a second or two to set up. For the effort you will remove lots of pain from your monthly tracking system.

The Expense Tracking Sheet

As powerful as SUMIF is, it does require that you record some additional information when you input all your data into your system. My personal expense tracker looks like this:

This has become my bread and better format for tracking financial information. When I started tracking how much my family was spending on our pregnancy , I essentially copied the above into a new spreadsheet and started logging away. The real secret behind using this style of tracking is that it fits with the SUMIF function rather well. The columns to the right of the “Amount” are really key here. The “Account,” “Sub Account,” and “C?” allow me to:

• Categorize the transaction into one of my 11(12) major budgeting categories
• Make notes on the transactions or add additional information such as the confirmation number or a special note. You can even use these fields to mark a transaction as occurring by check, debit, cash, or credit.

Tracking sheets are the first step to using SUMIF properly. Your categories should be clear and relatively few in number (having 50 categories will just give you a headache, stick with just a handful). If you don’t have a clean and easy to use tracking sheet it might be a good idea to make what you are using better.

Using SUMIF

Now that you have your tracking sheet in tiptop shape it is time to flex your spreadsheet muscles (pronounced musk-lz). I usually like to use my SUMIF function on a different sheet (tab) than the one I make my list in. This helps reduce some of the clutter and lets me view information in a clean space. The sheet where I use SUMIF the most is my budget tab as seen below:

Behind the numbers in the “To Date Spending” column is the power of SUMIF. The function automatically and continually updates itself as I add new transactions or recategorize existing information. The workings of the equation are pretty straight forward, but its power is shrouded in a veil of mystery to the new user. To demystify SUMIF, let’s take a quick glance at the structure of the function as you will have to enter it into the cell to unleash its power. This example is taken directly from the cell that contains what we have spent on food to date:

=SUMIF( Expenses!E$6:E$80 ,B4 ,Expenses!D$6:D$80 )

Function – all functions begin with an “=” and are then followed by the name of the function. The parameters of the function are contained inside the parentheses. Every function has its own set of parameters that are required for it to function properly, so you need to be familiar with what they are so that you know what to put between the parentheses. Both Excel and Open Office have a button that will walk you through the parameter portion of writing functions. It is located on the toolbar and looks like the image found on the right. Other useful functions for financial tracking are SUM, AVERAGE, COUNT, VLOOKUP, and IF. Each have their own set of parameters.

Range – is the range of cells that you want to evaluate for you SUMIF function. Ranges in both Excel and Open Office list the starting cell and ending cell separated by a colon (starting_cell:ending_cell i.e E6:E80). Since this is on a different sheet than the one I am trying to use, the name of the sheet appears first. In Excel the name should appear as shown above, but in Open Office the name would show up as “$Expenses.” – both mean the same thing in their respective programs. In my example above you will also notice the dollar sign ($). Since Excel and Open Office Calculate are relational, this means that when I copy the cell into another cell that the numbers (rows) that the function looks at remains the same. This is important if you want to use the same equation multiple times without having to rewrite the equation every time. If I were to put a dollar sign in front of the column identifier (E) this means that no matter where I copied the equation it would always look at E6 through E80 for the SUMIF function.

Criteria – is the condition or criteria (it can even be another function if you want it to be!) that you want to have added together. It can be in the form of a number (50), expression (<4), or text (Food). Since I am trying to categorize my purchases this is going to be the specific category that I want to view my spending for. Since I have all the categories already listed in the cell to the right of the SUMIF function, I simply call that cell by inserting the B4 into the cell. You don’t have to do it that way, but if you don’t you have to type “category_name” (with the quotes) in every single equation you are using. If you do the cell name way, and you use the $ tip, you can easily copy and paste the function into a new cell and not have to do any more work. Easy as cheesy.

Range Sum – This is the range of values that you want to sum if the value in the range equals what you are looking for (hence the name SUMIF). If a matching value is found in E6:E80 then it will return the corresponding value in D6:D80. If you leave this part of the equation blank then it will sum the Range .

If you are anything like me, the best thing to do now is play around with the equation like crazy. By fiddling you will gain some much needed experience and learn how to build your very own equations. Once you have it down, you can incorporate the SUMIF function into your monthly tracking sheets and easily keep track of your monthly budget without having to do any additional work. It is a clean and effective way to summarize and categorize your raw financial information into meaningful statements of spending. SUMIF helps you make a financial forest of all your little transaction trees.

Questions? Comments? Leave a comment below or shoot me an email using our contact form .