Formula Help SUM Function

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.

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