These 8 Google Sheets Formulas Simplify My Budgeting Spreadsheet (2024)

Budgeting spreadsheets are powerful tools for managing your finances, but with all the data and calculations involved, they can quickly become overwhelming. Fortunately, Google Sheets offers a range of formulas that can streamline your budgeting process. These formulas help you track expenses, manage income, and stay on top of your financial goals.

1 AVERAGEIF

The AVERAGEIF function helps you calculate the average of a group of numbers in a range of cells that meet specific criteria. The syntax for this function is:

=AVERAGEIF(range, criterion, [average_range]) 

Where range is the range of cells you want to evaluate, criterion is the condition that must be met, and [average_range] is the range of cells to average.

AVERAGEIF Example Calculation

Let’s assume you've decided to create a budgeting spreadsheet that tracks various expenses along with their corresponding dates, like in the example below.

To find the average amount you spend on groceries, where "Groceries" is listed in column A and the amount in column B, use the formula:

=AVERAGEIF(A:A, "Groceries", B:B) 

See Also

How to Match in Excel using MATCH and INDEX functions (With Example)19 Ways to Use Excel's New Checkboxes Feature - Excel CampusBest Excel formulas and functions to improve your spreadsheet productivityHow to create Excel drop-down lists that adapt to your data

Based on the sample data, the average amount spent on groceries is $150.

2 SUMIF

SUMIF allows you to sum the values in a range that meets specific criteria. The syntax for the SUMIF function is:

=SUMIF(range, criterion, [sum_range]) 

Where range is the range of cells to evaluate, criterion is the condition to meet, and [sum_range] is the range of cells to sum.

SUMIF Example Calculation

If you want to total your grocery expenses, use the formula:

=SUMIF(A:A, "Groceries", B:B) 

In this case, the total amount spent on groceries is $450, based on the sample data.

3 COUNTIF

With COUNTIF, you can count the number of cells in a range that meets specific criteria. This makes it easier to track the frequency of certain expenses.

The syntax for the COUNTIF function is:

=COUNTIF(range, criterion) 

Where range is the range of cells to count, and criterion is the condition that must be met.

COUNTIF Example Calculation

To count how many times you've shopped for groceries, use the formula:

=COUNTIF(A:A, "Groceries") 

Based on the sample data, the formula returns 3, meaning I made grocery purchases three times. If you want to input multiple criteria, you can use the COUNTIFS function.

4 IFS

IFS is a more advanced function that allows you to test multiple conditions. It’s useful for categorizing expenses based on different criteria.

The syntax for the IFS function is:

=IFS(condition1, value_if_true1, [condition2, value_if_true2], ...) 

Condition1 is the first condition to evaluate, and value_if_true1 is the result if that condition is true. You can add more conditions and corresponding results.

IFS Example Calculation

If you want to categorize your spending based on amounts, use the formula:

=IFS(B2<50, "Low", B2<100, "Medium", B2>=100, "High") 

This formula will label each expense as Low, Medium, or High based on its amount. For example, a $150 grocery purchase would be categorized as High, as shown in the screenshot below.

5 TEXT

The TEXT function formats numbers as text, which is useful for displaying numbers in a readable way (like showing currency or percentages).

The syntax for the TEXT function is:

=TEXT(value, format_text) 

Where value is the number you want to format, and format_text is the desired format (such as currency or percentage).

TEXT Example Calculation

To display a number as currency, use the formula:

=TEXT(B2, "$#,##0.00") 

Since cell B2 contains 150, the TEXT formula displays it as $150.00 to clarify that the cell's value represents money.

6 INDIRECT

The INDIRECT function allows you to reference cells dynamically by converting a text string into a cell reference. This allows you to update your formulas based on varying inputs, such as different sheets or ranges within your spreadsheet.

The syntax for the INDIRECT function is:

=INDIRECT(ref_text, [a1]) 

Where ref_text is a reference supplied as text, and [a1] is an optional argument that specifies whether the reference should use A1 style (TRUE) or R1C1 style (FALSE). By default, [a1] is set to A1 style (TRUE).

INDIRECT Example Calculation

Suppose you have a reference to a cell in text format (e.g., "B2"), and you want to convert it into an actual cell reference. To do that, use the formula:

=INDIRECT("B2") 

This formula returns the value in cell B2, which is $100.

Now, let's say you have multiple sheets in your budget workbook, each representing a different month (e.g., "January," "February," "March"). For instance, below is a screenshot showing the sample data for the monthly expenses in March:

The total amount is indicated in cell B7, and it's $1,775.

Let's say you want to create a summary sheet that displays the months in column A and dynamically pulls in the total expenses from any given month and writes them in column B. Below is an example of what the sheet would look like.

Now, assume you want to dynamically pull the total expense from cell B7 in the March sheet and display it in your summary sheet. Here's how you can use the INDIRECT function to do that:

=INDIRECT("'" & A4 & "'!B7") 

In this example, A4 refers to the cell in your summary sheet containing the name of the sheet you want to pull your data from (e.g., "March"), and the formula dynamically references cell B7 from the March sheet, which contains the total expenses for the month. The ampersand (&) is used to concatenate or join text strings together.

In this case, it combines the single quote (') for sheet names with spaces, the sheet name from A4, and '!B7', where the exclamation mark (!) separates the sheet name from the cell reference.

The screenshot shows that the total monthly expenses for March resulted in $1,775 when using the INDIRECT formula. This corresponds to the total monthly expenses in the March sheet itself. Interestingly, changing the value in A4 to "February" will automatically update the reference to the February sheet.

7 FILTER

The FILTER function allows you to filter a range of data based on specific conditions—making it easy for you to isolate particular expenses or categories.

The syntax for the FILTER function is:

=FILTER(range, condition1, [condition2], ...) 

Where range is the data you want to filter, and condition1 and condition2 are the conditions that the data must meet.

FILTER Example Calculation

Here’s how to use the FILTER function to filter out only grocery expenses using our sample data:

=FILTER(A:B, A:A="Groceries") 

Enter the formula in a new column. In my example, I entered it into cell E2 in column E. The formula will return only the rows where column A contains "Groceries," which would allow you to focus on your grocery spending without distraction.

8 XLOOKUP

XLOOKUP is a versatile function that searches a specified range for a match and returns a corresponding value. It’s perfect for looking up expenses or categories.

The syntax for the XLOOKUP function is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

Where lookup_value is the value to search for, lookup_array is the range to search, return_array is the range to return the result from. The optional parameters help specify what to do if no match is found and how to handle matching criteria.

XLOOKUP Example Calculation

Suppose you have a budget spreadsheet that tracks various expenses by category in column A, the amount spent in column B, and item names in column C. Now, you want to determine how much you spent on a specific grocery item, such as Vegetables.

Here’s the XLOOKUP function that can help you with this:

=XLOOKUP("Vegetables", C:C, B:B) 

In this example, the formula returned $150, the amount spent specifically on Vegetables

Now, all the formulas we’ve covered can transform your budgeting process and make it easier to manage your finances effectively. Try them out today and discover how they can streamline your budgeting tasks.