SUMIF: for the serious budgeteer

In my initial Welcome post I made bold promises about creating budgets, which is one area that people from all walks of Excel experience would like to improve upon. So here we are and I haven't even thrown down the most important function for creating a sweet budget: SUMIF.

As its name implies, SUMIF will take a conditional sum. This means that in an array of numbers, SUMIF will take the sum of all the numbers that meet a specified criterion. In the case of the budget, which is what I'll focus on, you may have a list of expenses in one column and a category tag in another column that identifies the expense (Rent, Groceries, Drinking, etc.). Through intelligent use of this function, you can easily and accurately create a summary of your financial life (or aspirations if you're doing projections).

Let's dive into the SUMIF syntax (download the example worksheet here):

=SUMIF(range,criterion,[sum_range])

1. Range: The 'range' is the group of cells that will hold the condition. In our example above, it would be the column that contains the category tags.

2. Criterion: This is the value that the function is looking to match in the 'range'. So if you wanted to find the total of all of your "Drinking" expenses, your criterion would be "Drinking".

3. Sum Range: This is another group of cells that holds the numbers that you want to Sum. In the budget example, this would be the column that shows how much you have spent on each thing. Important note: the 'sum range' and 'range' should have the same dimensions and should line up perfectly. For example, if your 'range' is $A$2:$A$40, then your 'sum range' should be $B$2:$B$40 (assuming the numbers are in column B). This is good practice and keeps it clean.

Putting it all together: So the function takes your 'criterion', which we're saying is "Drinking" and will look cell by cell in the 'range' and will take the sum of every cell in the 'sum range' that lines up with a cell in the 'range' that says "Drinking". Importantly, the match has to be exact - if you put "Drinks" in one of the cells, the function will not recognize it and will skip over the row. So make sure to categorize expenses while you're sober and ready to be consistent.

As is customary at EfP, I will take a second to tell you to use references rather than text (like "Drinking"). Rather than write a different function for each of your expense categories, why don't you use a reference to a cell that contains the word "Drinking". This way you can write the function once and fill it down for all of your other expenses - if you have any other expenses. Remember: don't put " "s around references; mind your relative and absolute references (you will generally want your ranges to be absolute and your criterion to be relative); and DO put " "s around your criterion if it is not a reference.

You may have noticed above that the 'sum range' argument is in square brackets, indicating that it is optional. You are indeed a keen observer; the 'sum range' is optional in the case that it is identical to your 'range'. This will happen if you are looking, for example, to take the sum of only numbers that are greater than a certain number. In this case you are essentially looking for your 'criterion' in your 'sum range', and putting it in twice would be redundant. But it won't hurt to put it in. Unless anything other than perfect efficiency hurts you.