The formulas that carry a budget in Google Sheets are SUM to total a range, SUMIF and SUMIFS to add spending by category, IF to flag overspending, and QUERY to aggregate a transaction list. This guide gives the syntax and a worked example for each, plus date functions, the common error codes, and combinations for net income and savings rate.
Formulas automate budget calculations - totals update instantly when you change data. Master these and your spreadsheet does the math.
Don’t want to build from scratch? The Monthly Budgeting Template has some of this wiring in place already: SUMIF pulls each category’s actual spending out of the transaction log, COUNTIF counts the entries flagged as subscriptions, and IFERROR keeps the percentage columns readable when a category has nothing planned.
Essential Formulas
These are the basics you’ll use constantly. SUM adds numbers in a range: =SUM(B2:B15). Use it for total expenses, total income, category subtotals. It’s the workhorse of any budget spreadsheet.
AVERAGE finds the mean: =AVERAGE(C2:C13). Useful for average monthly spending or calculating typical utility bills from historical data. Subtraction is simple: =B5-B20 calculates net income (income minus expenses) or budget remaining. Multiplication and division convert between time periods: =B2*12 converts monthly to annual, =C5/4 converts quarterly to monthly.
Conditional Formulas
Conditional formulas add intelligence to your spreadsheet. IF shows different results based on conditions: =IF(C5>B5, "Over Budget", "On Track"). This flags overspending instantly.
SUMIF adds numbers that match a criterion: =SUMIF(A2:A50, "Groceries", B2:B50). This totals spending by category from a transaction list.
A category-tagged transaction list, the shape SUMIF reads from - the Monthly Budgeting Template (Premium).
SUMIFS handles multiple conditions: =SUMIFS(C2:C100, A2:A100, "Dining", B2:B100, "January"). This shows category spending for a specific month. COUNTIF counts matching entries: =COUNTIF(A2:A50, "Restaurant"). Useful for tracking transaction frequency.
Date Formulas
Date formulas automate time-based calculations. TODAY returns the current date: =TODAY(). It updates automatically each day, useful for date stamps and calculating days remaining.
MONTH extracts the month number from a date: =MONTH(A2). This helps group transactions by month. EOMONTH finds month boundaries: =EOMONTH(TODAY(), 0) returns the last day of the current month. Use it for bill due dates and budget period endings.
Advanced Formulas
These require more setup but add significant power, and they are the backbone of an advanced personal finance tracker in Google Sheets. VLOOKUP finds values in a table: =VLOOKUP("Rent", A2:C20, 3, FALSE). It looks up “Rent” in column A and returns the value from column C.
QUERY is Google Sheets’ most powerful function: =QUERY(A1:D100, "SELECT A, SUM(D) WHERE B='January' GROUP BY A"). It filters and aggregates data using database-style syntax. GOOGLEFINANCE pulls live financial data: =GOOGLEFINANCE("AAPL", "price") for stock prices, =GOOGLEFINANCE("CURRENCY:EURUSD") for exchange rates. Useful for investment tracking.
Practical Combinations
Real budgets combine formulas for useful calculations. Net income subtracts expense total from income: =SUM(B2:B10)-SUM(C2:C30). Savings rate shows what percentage of income you’re saving: =(B2-SUM(C2:C30))/B2*100.
Over/under status with amount shows both the status and how much: =IF(C5>B5, "OVER by "&TEXT(C5-B5,"$#,##0"), "Under by "&TEXT(B5-C5,"$#,##0")). Category as percentage of total helps understand spending distribution: =SUMIF(A:A,"Groceries",B:B)/SUM(B:B)*100.
Common Errors
Errors happen, and understanding them helps fix problems quickly. #REF! means a reference to a deleted cell - check that all referenced cells still exist. #VALUE! indicates wrong data type - ensure cells contain numbers where the formula expects numbers.
#DIV/0! means you’re dividing by zero. Use IF to check first: =IF(B2=0, 0, A2/B2). #N/A usually means VLOOKUP can’t find a value. Check spelling, or use IFERROR to handle missing values gracefully: =IFERROR(VLOOKUP(A2,D:E,2,FALSE), "Not Found").
Tips
A few practices make formulas easier to work with. Lock references using $ to prevent cells from shifting when copying. $A$1 locks both column and row. Name ranges for readability - instead of =SUM(B2:B50), name the range “Income” and use =SUM(Income).
Format as currency by selecting cells, then Format, then Number, then Currency. This makes numbers readable without adding manual dollar signs. Start simple - a working =SUM() is better than a broken complex formula. Build complexity gradually.
More Google Sheets Tutorials
- Conditional Formatting for Budget Tracking - Visual alerts for overspending
- Create Dropdown Lists for Budgeting - Standardize category entry
- Budget Dashboard in Google Sheets - SPARKLINE charts and visual summaries
- How to Lock Cells in a Budget Spreadsheet - Protect formulas from accidental edits
- Import Bank Statements to Google Sheets - CSV import workflow
- 15 Essential Google Sheets Formulas for Personal Finance - the wider 15-formula reference, including XIRR and ARRAYFORMULA
- Google Sheets Formulas - Full formula reference library
Related
Frequently asked questions
Do formulas update automatically?
Yes. Change the data, the result updates instantly.
Do these work in Excel?
Most do. GOOGLEFINANCE and QUERY are Google Sheets-specific.
Most useful formula?
SUMIF. Simple to learn, powerful for categorizing expenses.
Why does my SUMIF return 0 when the category is clearly in the list?
Usually a text mismatch. A trailing space, a different capitalization, or a number stored as text will stop a criterion from matching. Trimming the category cells or picking the value from a dropdown keeps the text identical to what SUMIF looks for.
How do I apply one formula down a whole column without copying it into every row?
Wrap it in ARRAYFORMULA, for example =ARRAYFORMULA(B2:B*12), so a single formula in the header row spills a result down every populated row. New rows are covered automatically, which is why many pre-built templates use it instead of copying a formula into hundreds of cells.
Does GOOGLEFINANCE work for every currency and stock?
It covers most major tickers and currency pairs, but coverage is not universal and prices can lag by up to 20 minutes. It also only exists in Google Sheets, so a workbook that relies on it will break the formula if it is opened in Excel.
Sources
- Google Docs Editors: Function list - Google
- QUERY function - Google
- GOOGLEFINANCE function - Google
About this article
Every formula in this guide was checked in Google Sheets against Google's published function reference for syntax and argument order. Product claims checked on 2026-09-10 against the shipped Monthly Budgeting Google Sheet (Summary, Budget Plan and Transactions tabs). Last reviewed September 2026.