All You Need to Know about Airtable’s Rollup Feature (in 2024)
Contents
Airtable is an incredible tool that brings together the best of automation and database-building. Its functionalities give you a tremendous amount of control over your data and even allows you to integrate with no-code, low-code, and enterprise tools.
The rollup feature is one of many that allows users to manipulate data into what they need. Taking the lookup tool one step further, rollup fields allow you to perform calculations, create aggregations, and build formulas from values linked to another field.
But while the rollup feature is a time-saver, it can also be confusing to use. In this article, we’ve pulled together all you need to know about this feature including how to set it up and when to apply different aggregate functions.
How to Use Airtable’s Rollup Feature
Rollup fields make it possible to manipulate data in a single step rather than create multiple fields to link tables, limit values, and apply formulas. They are most appropriate when values are collected in one table but a summary needs to be referenced in another.
To add a rollup field to your base, you need to know where the fields you want summarized are located, what aggregation function is needed to calculate the final values, and if any needed limitations need to be set to eliminate inaccurate outputs.
The following steps will help you apply formulas to linked values using the rollup feature:
- Add a new “Rollup” field type to your table
- Select the linked table containing the fields you want to summarize
- Choose the field you want to summarize
- (Optional) Insert conditions to limit the values you want to summarize
- Insert a rollup aggregation function
- (Optional) Specify the format you want your data delivered in
A rollup field type configuration box should look something like this:
Note that a rollup field requires a link between the two tables. If your tables aren't connected yet, add a column and link it to data that can be found in the primary key field of the other table. You should then be able to add a rollup field and configure it according to your needs.
Bear Tip 🐻: If the linked values aren't important to the table, hide it. This will not affect the calculations in your rollup field.
Having greater control over your output means calculations are more accurate, data is in the correct format, and automations are improved.
Aggregate Functions to Use with the Rollup Feature
Setting up a rollup field isn’t too complicated. What most people find to be tricky is deciding which function is most appropriate for their use.
Airtable gives users a wide range of formulas with which to manipulate their data. The rollup feature, however, can only be used with 14 of them. Understanding what each one does will help you select the best for your needs.
To illustrate the many capabilities of the rollup function, we will be using a hypothetical construction company database throughout the article.
Let’s go over each rollup-compatible function and a simple example of how it works.
AND / OR / XOR
The AND, OR, and XOR functions are cousins with similar function, but one may match your needs one more than the other. They can be useful if you would like to see if all conditions have been met for the next event to take place, such as when building circuits or seeing whether a product order should go through.
AND(): Returns true if all of the arguments are true, and returns false otherwise
OR(): Returns true if any one of the arguments are true, and returns false otherwise
XOR(): Returns true if an odd number of the arguments are true
🧵 EXAMPLE : Let’s use the AND function to check whether each client has provided all project descriptions.
Screenshots of Airtable AND() function configuration
We can see that the first field returns true (1), indicating that details have been provided and there is no need to follow up with this client. The second field, however, returns false (0). Follow up will be necessary before projects can kick off.
ARRAYCOMPACT / ARRAYJOIN / ARRAYUNIQUE
ARRAY functions deliver a set of values after adjusting them according to your specifications. This can help you remove empty values, join items into strings, and disregard duplicates.
ARRAYCOMPACT(): Removes any null or empty values from an array
ARRAYJOIN(): Joins array items into a string with a separator
ARRAYUNIQUE(): Returns unique array items only
🧵 EXAMPLE : Let’s use the ARRAYUNIQUE function to deliver only unique client emails from an array.
Screenshots of Airtable ARRAYUNIQUE() function configuration
We can see that there are no duplicated emails in the output, specifically for the third contractor, Samira Tulog. If you have automated emails set up, using this function prevents clients from receiving a barrage of emails for every order they placed when they should have received just one.
CONCATENATE
CONCATENATE allows you to join data and/or static text together from linked fields. This can help you autogenerate order reference numbers, email addresses, and much more.
CONCATENATE(): Joins data and/or static text into a single text value
🧵 EXAMPLE : Let’s use CONCATENATE to generate a custom reference code for each project.
Screenshots of Airtable CONCATENATE() function configuration
The output produced is unique as it combines client name and project name. It can then be used to specify a particular project in an email or invoice.
COUNT / COUNTA / COUNTALL
COUNT functions add up the number of items and deliver it as an integer. The only difference between these three functions is whether you want to count integers only, integers and text, or all items.
Having an active count of a field in a database can give you a glimpse at summarized information, such as see how many outstanding invoices need to be collected or how much stock remains of an inventory item.
COUNT(): Counts the number of numeric items
COUNTA(): Counts the number of non-empty numeric and text items
COUNTALL(): Counts the number of all items, including numeric, text, and empty elements
🧵 EXAMPLE : Let’s use COUNTA to see how many active projects each client has in progress.
Screenshots of Airtable COUNTA() function configuration
Because project names are text items and we don’t want to include empty elements, we don’t use the COUNT or COUNTALL functions. We will also apply a limitation that removes any completed projects from the calculation.
Results show that the first client has one active project, even though they have two items in the "Projects" field. This is because one of the projects has already been completed and is not counted.
MAX / MIN
The MAX and MIN functions are relatively straightforward—they return the greatest or smallest value from a set of items. This can help you sort for upcoming deadlines, highest or lowest ticket items, and more.
MAX(): Returns the greatest of the given values
MIN(): Returns the smallest of the given values
🧵 EXAMPLE : Let’s use MIN to see which project is due first for each contractor.
Screenshots of Airtable MIN() function configuration
The function returns a single date—the earliest date—despite several contractors having more than one ongoing project.
AVERAGE / SUM
AVERAGE and SUM can summarize a long list of integer values, making it useful for tracking sales, inventory, and even number of employees needed on a job site.
AVERAGE(): Calculates the mean average of the values
SUM(): Calculates the total sum of the values
🧵 EXAMPLE : Let’s use the SUM function to calculate each client’s cumulative project budget.
Screenshots of Airtable SUM() function configuration
The function returns the sum of all projects, giving us a glimpse of the value of each client. Using the AVERAGE function can show how much, on average, each project is worth. This data can be useful for projecting future orders.
Bear Tip 🐻: Need a closer look at how these rollup formula examples work? View the full sample database here!
Applying Limitations to Your Rollup Aggregations
Sometimes, you may need to limit the values in a data set to ensure the return is accurate. Examples of such situations are:
- When you don’t want outliers to influence averages
- When you don’t want to count empty values
- When anything beyond a certain date or maximum cap should be disregarded
You can apply conditions to your rollup fields so no additional formula columns are needed.
To add a limitation, toggle on the option: “Only include linked records from the {Table name} table that meet certain conditions" and insert your requirements.
Save your field as usual, and check your results.
Streamline Your Airtable Database with Rollup Fields
The rollup feature combines a few invaluable Airtable functionalities: linking tables together, looking up relevant values, and applying a formula to summarize data according to your needs. If you have yet to use it in your databases, there's no doubt that you will soon find a reason to!
To get even more out of Airtable, check out a few of our tutorials:
👉 A 5-Minute Quick Guide to Working with Airtable Views
👉 How to Pre-populate Airtable Form Fields with Dynamic URL Parameters
👉 5 Practical Ways to Import Data into an Existing Airtable Base
