5 Advanced Formulas To Build a Simple Monthly Budget Template

• AVERAGEIF
• SUMIF
• COUNTIFS
• INDIRECT
• IMPORTRANGE

AVERAGEIF

• Calculates the average of values in a range of cells that meet a certain criteria
• Useful for calculating the average amount spent on a specific category, such as groceries
• Can be used to find out how much is spent on a particular item, like cereal

SUMIF

• Adds up values in a range of cells that meet a certain criteria
• Useful for adding up all expenses for a particular category in a given month
• Can be used to find out how much is spent on a specific item, like Legos

COUNTIFS

• Counts the number of cells that meet multiple criteria.
• Useful for counting the number of times money was spent on a particular category in a particular month
• Requires consistency in categorizing expenses, such as choosing between "eating out" and "dining out"

INDIRECT

• References a range of cells based on the value of another cell
• Useful for referencing the corresponding column for each category in a budget formula
• Can be used to programmatically construct sheet names and references in formulas

IMPORTRANGE

• Imports data from a different Google Sheets document or a different spreadsheet file
• Useful for including information from a separate sheet that tracks income in a budget sheet
• Requires granting access to the other sheet and specifying the sheet name and range of cells to import.

Why are you building a monthly budget template?

Because you want to track your expenses, manage your finances, hoping to save money, plan for the future, and generally during the month you want to reduce stress.

Why build instead of buy a template?

Because you can buy someone else’s budget template and start using that. But it just won’t fit your needs quite so tightly. You’ll still have to move stuff around, account for your own family’s specific needs. Maybe the budget template is made by a 20 something single finance bro in New York. But your Mother-in-Law lives in your extra room to take care of the kids during the summer while you and the wife run a little boutique airbnb with boat rentals at the lake.

You want something that fits you, and fits your changing needs. Maybe another kid is on the way. Or you’re getting older and your 17 year old daughter is moving to college. You still need to support them, until they get that college job on campus.

What’s in your Template?

Simply you’ll have to make a sheet with a template so you can copy it every month for a new month to plan and thus account for.

You’re going to list your expenses and your income each month. Salary plus those extra ways your family is making money. The side job, the side-hustles, the dividend payouts.

You already know

You know these formulas: SUM, AVERAGE, MAX, MIN and you’re comfortable with them. You’ve got them all over your sheet. But do you know SUMIF? Advanced Google Sheet enthusiasts can't figure out SUMIF. It's a frustrating formula.

Here are 5 advanced formulas that I think can make your simple budget template an absolute banger.

Use the AVERAGEIF formula to calculate the average of values in a range of cells that meet a certain criteria. For example, if you want to calculate the average amount spent on groceries in a given month, you can use the AVERAGEIF formula to calculate the average of all of the amounts categorized as "groceries."

Heck, go deeper and find out how much the wifey spends on those delicious cereals you eat at 11pm reading articles on how to budget better in Google Sheets.

Use the SUMIF formula to add up values in a range of cells that meet a certain criteria. For example, if you want to add up all of the expenses for a particular category in a given month, you can use the SUMIF formula to add up all of the expenses that are categorized as that category. Finally, you can find out how much your kids spend on legos. Whoops that you spending that much on Legos! That 5 year old ain’t gonna build the Death Star on his own.

Use the COUNTIFS formula to count the number of cells that meet multiple criteria. For example, if you want to count the number of times you spent money on dining out in a particular place, in a particular month, you can use the COUNTIFS formula to count the number of cells in the "dining out" column that have a date within that month.

It’s up to you if you want to count McDonald’s as dining out. And it’s up to you to make sure you don’t categorize items as “eating out” and “dining out”. Just decide one, and it’ll make your budgeting life easier.

Use the INDIRECT formula to reference a range of cells based on the value of another cell. For example, if you have a dropdown list of categories and want to reference the corresponding column for each category in your budget formula, you can use the INDIRECT formula to reference the column based on the selected category.

INDIRECT is a funky function that if you’re not familiar with, it’s awesomely useful. You can reference another cell’s value as the reference, or you can use indirect to programmatically construct the sheet name and reference the sheet in a formula without having to type it in each time.

This is incredibly useful when you want to construct a summary page and want to reference one cell on every tab but they are labeled the months. So you know sheet “JAN!A1” and “FEB!A1” you need to get..oh and every other month. Use “INDIRECT()” as a wrapper and then add in a reference to the month, INDIRECT(MONTHHERE &”!A1”) is an example.

Use the IMPORTRANGE formula to import data from a different Google Sheets document or a different spreadsheet file. For example, if you have a separate sheet that tracks your income and want to include that information in your budget, you can use the IMPORTRANGE formula to import the data from the income sheet into your budget sheet. This formula requires you to grant access to the other sheet and requires you to specify the sheet name and range of cells you want to import.

This is incredibly useful if you want to import the last year’s final summary data into this year’s data. To start a year, you can grab the data from that other file, pull it into your year template this year and start fresh, with old data.

This makes templating so much easier instead of having to keep adding every month forever to one sheet.

You can copy the whole sheet, clear out the data, and import the end of last year. Done.

Summary

Buying a pre-made budget template may not fit specific needs and circumstances, so it's better to create a customized one.

The template should include a list of monthly expenses and income sources, using basic formulas like SUM, AVERAGE, MAX, MIN.

Advanced formulas like AVERAGEIF, SUMIF, COUNTIFS, INDIRECT, and IMPORTRANGE can also be used to make the budget template more effective.

For instance, AVERAGEIF can calculate the average spent on a particular category like cereal, while COUNTIFS can count the number of cells that meet multiple criteria.

INDIRECT can reference a range of cells based on the value of another cell, while IMPORTRANGE can import data from a different Google Sheets document or a different spreadsheet file.

Using these advanced formulas can make budgeting easier and more efficient.