The five formulas every money spreadsheet needs

You don't need to be a spreadsheet expert to manage your money effectively. A handful of formulas do most of the heavy lifting in any budget or money management spreadsheet. Learn these five, and you can build almost any financial tracker you need.

1. SUM — the total

The most basic formula, and the one you'll use most:

=SUM(D2:D100)

This adds up all values in cells D2 through D100. Use it to total your expenses, income, or any list of numbers.

Variations

• Sum a row: =SUM(B2:H2) — totals a row instead of a column
• Sum an entire column: =SUM(D:D) — totals all of column D, no matter how many rows
• Sum with conditions: see SUMIF below

Where to use it

• Total monthly expenses
• Total income for the period
• Total savings contributions
• Total debt payments

2. SUMIF — the conditional total

SUMIF adds up values that meet a condition. It's the formula that makes category-based budgeting work.

=SUMIF(C:C, "Food", D:D)

This says: "Sum all values in column D where the value in column C is 'Food'."

The three parts

1. Range (C:C) — the column to check for the condition
2. Criteria ("Food") — what to match
3. Sum range (D:D) — the column to add up when the condition is met

Example: expense by category

| Date | Description | Category | Amount | |---|---|---|---| | 2026-10-01 | Coles | Food | -145 | | 2026-10-02 | Shell | Transport | -65 | | 2026-10-03 | Coles | Food | -89 | | 2026-10-04 | Netflix | Entertainment | -23 |

To total all Food expenses:

=SUMIF(C:C, "Food", D:D)

Result: -234 (the sum of -145 and -89)

SUMIF with number conditions

You can use conditions other than text matches:

• Greater than: =SUMIF(D:D, "<-100") — sums all expenses over $100
• Date range: =SUMIF(A:A, ">=2026-10-01", D:D) — sums transactions on or after October 1

SUMIFS — multiple conditions

When you need to match more than one condition, use SUMIFS:

=SUMIFS(D:D, C:C, "Food", A:A, ">=2026-10-01", A:A, "<=2026-10-31")

This sums all Food expenses in October 2026 — matching both the category and the date range.

3. IF — the decision formula

IF lets you display different values based on a condition. It's the formula that makes spreadsheets "smart."

=IF(D2 > 0, "Income", "Expense")

This says: "If D2 is greater than 0, show 'Income'; otherwise, show 'Expense.'"

The three parts

1. Condition (D2 > 0) — what to test
2. Value if true ("Income") — what to show if the condition is met
3. Value if false ("Expense") — what to show if it's not

Example: over/under budget

| Category | Budget | Actual | Status | |---|---|---|---| | Food | $400 | $420 | ? | | Transport | $150 | $120 | ? |

The Status formula:

=IF(C2 > B2, "Over budget", "Under budget")

Result: Food shows "Over budget" (420 > 400), Transport shows "Under budget" (120 < 150).

Nested IF — multiple conditions

You can nest IF statements for more than two outcomes:

=IF(C2 > B2 * 1.1, "Significantly over", IF(C2 > B2, "Slightly over", "Under budget"))

This shows "Significantly over" if actual is more than 10% above budget, "Slightly over" if above budget, and "Under budget" otherwise.

4. VLOOKUP — the lookup formula

VLOOKUP finds a value in one column and returns a corresponding value from another. It's how you pull information from a reference table.

=VLOOKUP(A2, Reference!A:B, 2, FALSE)

This says: "Look up the value in A2 in the first column of the Reference sheet, and return the value from the 2nd column of that sheet."

Example: bill amounts

Suppose you have a reference sheet of bills:

| Bill | Amount | |---|---| | Rent | $800 | | Electricity | $180 | | Phone | $60 |

In your main sheet, you type the bill name and VLOOKUP fills in the amount:

| Bill | Amount | |---|---| | Rent | =VLOOKUP(A2, Reference!A:B, 2, FALSE) → $800 | | Phone | =VLOOKUP(A3, Reference!A:B, 2, FALSE) → $60 |

The four parts

1. Lookup value (A2) — what to search for
2. Table range (Reference!A:B) — where to look
3. Column index (2) — which column to return (2 = the second column in the range)
4. Exact match (FALSE) — always use FALSE for exact matches; TRUE allows approximate matches, which can cause errors

XLOOKUP — the modern alternative

If you're using a recent version of Excel or Google Sheets, XLOOKUP is simpler and more powerful:

=XLOOKUP(A2, Reference!A:A, Reference!B:B, "Not found")

It doesn't need a column index and handles errors gracefully.

5. DATEDIF — the time formula

DATEDIF calculates the difference between two dates. It's useful for tracking how long until a bill is due or how long you've been saving.

=DATEDIF(A2, TODAY(), "d")

This says: "Calculate the number of days between the date in A2 and today."

The three parts

1. Start date (A2)
2. End date (TODAY()) — or any other date
3. Unit ("d") — "d" for days, "m" for months, "y" for years

Example: days until a bill is due

| Bill | Due date | Days until due | |---|---|---| | Rent | 2026-10-15 | =DATEDIF(B2, TODAY(), "d") → 3 | | Electricity | 2026-10-28 | =DATEDIF(B3, TODAY(), "d") → 16 |

Example: months until a savings goal

If you're saving $200/month toward $2,400:

=ROUNDUP(2400 / 200, 0)

Or, if you have a target date:

=DATEDIF(TODAY(), A2, "m")

This shows how many months until the target date.

Bonus: Combining formulas

The real power comes from combining formulas. A few useful combinations:

Total expenses this month

=SUMIFS(D:D, C:C, "Expense", A:A, ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1))

Sums all expenses where the date is in the current month.

Remaining budget for a category

=B2 - SUMIF(Transactions!C:C, A2, Transactions!D:D)

Budgeted amount minus actual spending for the category in A2.

Savings rate

=SUMIF(D:D, ">0") / (SUMIF(D:D, ">0") - SUMIF(D:D, "<0"))

Income divided by net (income minus expenses) — your savings rate as a decimal.

Tips for working with formulas

• Start with =. Every formula begins with an equals sign.
• Use cell references, not numbers. =B2 + C2 is better than =800 + 180 — if the numbers change, the formula still works.
• Use absolute references with $. =$B$2 keeps the reference fixed when you copy the formula. Without $, references change relative to where you paste.
• Handle errors with IFERROR. =IFERROR(VLOOKUP(A2, Ref!A:B, 2, FALSE), "Not found") shows "Not found" instead of #N/A.
• Format cells. Right-click → Format cells → Currency for amounts, Percentage for rates, Date for dates.

When formulas aren't enough

Spreadsheets are powerful, but some financial tasks are easier with a dedicated tool:

• Balance projection — seeing your balance over the coming weeks requires a complex formula setup. The free payday calculator does this instantly.
• Runway and buffer alerts — knowing when your balance will breach your buffer needs daily recalculation. The Payday Compass app handles this automatically.
• Multi-currency tracking — travel money with exchange rates is possible in a spreadsheet but cumbersome.

For tracking and categorising transactions, a spreadsheet with these five formulas is excellent. For projection, runway, and alerts, a dedicated tool complements the spreadsheet.

Summary

Five formulas do most of the work in a money spreadsheet: SUM (totals), SUMIF (conditional totals by category), IF (decisions and status), VLOOKUP (lookups from reference tables), and DATEDIF (date differences). Master these, and you can build any budget, tracker, or financial planner you need. Start simple, use cell references, and combine formulas for more powerful calculations.

For projection and runway that go beyond what a spreadsheet easily does, try the Payday Compass Money Runway.