How to Use Rollup and Count Fields
The rollup field in Airtable is an excellent way to summarize data from a linked table in the same base. As the name implies, any time you want to roll data up to a larger “bucket”, the rollup field will be useful.
The Budget Example
I created this Budget template base that you can use to see how Rollup and Count fields work. In this base I have three tables:
- Transaction: The individual transactions (purchases) that have an amount and category
- Categories: The individual buckets that make up a budget. Every budget has one or more categories and allow granularity into where spending is taking place
- Budget: The high-level view of all spending and which money is allotted and tracked
On the transaction tab, I have a field linked to the categories tab. When I log a new transaction, I select which category that falls under. For instance, if I purchased $42.50 worth of gas at a Shell station, I would add the category of ‘Gas & Fuel’. If I had to get my brakes replaced for $670, that transaction would be categorized as ‘Vehicle Maintenance, Service, and Repair’.
These would be two separate transactions with two separate categories, but their total would ‘rollup’ to the budget of ‘Auto and Vehicles’. In this example, the Rollup field would show $712.50 (42.50 + 670).
Rollup Formula
When you create the field, you need to select the linked table, and the field from that table for which you want to add values. In my example for the Budget table, I want to get data from the Amount field of the linked Categories table. Although you can use different formulas for rollup, the main one (and the one used in this example) is SUM(values).
Count
The count field is similar to the rollup field except that instead of returning a combined value, it will just count how many times a linked record appears. In the example above, theses would be the following counts:
- Gas & Fuel: 1
- Vehicle Maintenance, Service, and Repair: 1
- Auto and Vehicles: 2
These formulas will help you get more insights from how different records in your base are adding up to the whole picture.
