Formula Help SUM Function

[John_Dlouhy]

Greetings! I am reaching out for assistance with a formula field (admittedly not my strong suit!).

I am tasked with tracking a declining balance fund used to pay for event expenditures. I have the following columns: item, estimated expense, actual expense, fiscal year.

Ideally there would be two additional formula fields that would subtract each new expense from the starting balance. As expenses are added, I would be able to see the declining balance.

My failed attempt: SUM(5000.00-{actual expense}). It appeared to be working for the first entry I made, but then went haywire when new entries were added to the table.

Any assistance would be greatly appreciate.

[Justin_Barrett]

If you’re coming from a spreadsheet paradigm, Airtable can take some time to get used to, mainly because of this one factor: unlike cells in a spreadsheet, database records (rows) have no knowledge of each other. They all share some common properties based on the way a table’s fields are designed, but one record doesn’t have any idea that another record exists. Because of that, doing calculations across multiple records is challenging. It’s not completely impossible, because there are some tricks that can be used to let records access the values from other records in the same table (long story short: by tying in another table), but it can get hairy quickly.

With that preface out of the way, here are two options to consider. The first doesn’t require any formulas, only a change in approach, which can be seen in the following screenshot:

In short, you enter a positive amount for the starting balance, and negative amounts for all expenses. The remaining balance is shown in the summary bar at the bottom of the table (I shrank my window so this bar would be much closer to the actual records). This is the simplest way to go, though it doesn’t give you a value that you can copy and paste anywhere else if needed. That summary bar is there for display only.

The other option you might consider involves adding another table, which I’ll call [Accounts] for this sample. Each record in that table will/can represent a different account that expenses from the [Expenses] table can be applied to. Here’s a sample [Accounts] table with a couple simple accounts:

Over on the [Expenses] table, it looks like this:

Each expense is linked to a single account. That link allows us to roll up those linked expense values on the [Accounts] table:

A formula field subtracts that expense summary from the {Starting Balance} value for that account, leaving us with the amount remaining. As the years progress, you add new account records for the appropriate fiscal years. Expenses can only link to a single fiscal year account, so you can keep each fiscal year’s expenses separately.

[John_Dlouhy]

Hello Justin!

I cannot thank you enough for the EXCELLENT guidance you have provided! From the thorough, easily understood explanations to the terrific screenshots—just tremendously helpful and truly appreciated.

John