Skip to content
Kuovonne's Guide to Airtable
  • Pages
    • Kuovonne's Guide to Airtable
      • Fields
        • Overview of fields types
          • Editable Field Types
          • Computed Field Types
          • Field Types by Formula Data Type
          • Field Types in Scripting
        • Specific field types
          • Formula fields
            • Demystifying Formulas (DareTable 2022)
          • icon picker
            Rollup fields
          • Select fields
          • Attachment fields
            • Uploading Attachments
            • Attachment Size
            • Attachment URLS
            • Thumbnails
        • Converting Field Types
        • Naming fields
        • Metadata fields
          • Simple Metadata Fields
          • Data Validation
          • Additional Dependencies
          • Original Record URL (Thumbprint) Field
          • Column Divider Fields
          • DareTable 2023 Presentation
        • Field dependencies
      • Formulas
        • Formula Field Reference
        • Writing Formulas
          • Using AI to Write Formulas
          • General tips for writing formulas
          • Testing for Blank Values
          • Drafting Formulas in Parts
          • Function versus Operator
          • Typing Special Characters
          • Commenting Formulas
          • Formulas to avoid
        • Formatting Formulas
          • Basic Rules
          • Nested IF formulas
          • SWITCH Formulas
          • Operators
        • Specific Formulas
          • Ranges of Numbers Values
          • Comma Separated List of Fields Values with Optional Fields
          • Earliest / latest date across columns
          • List Fields Values (one per line) with Optional Fields
          • Rounding a date to the half year
          • Url of Interface Page for a record
          • Removing varying items from a list
        • Data Type Conversion in Formulas
          • Text to Numbers
          • Numbers to Text
          • Data type conversions with Dates
        • Troubleshooting Formulas
          • Formulas that won't save
          • Hiding or showing #ERROR!
          • Troubleshooting date/time/duration formulas
          • Troubleshooting numeric formulas
          • Troubleshooting formulas with lookups
          • Troubleshooting text formulas
      • Automations
        • Triggering automations
          • Triggering automations for typed values
          • Triggering automations on existing records
        • Sending Emails
          • Ways to send emails
          • Using links in emails
          • Emailing Attachments
        • Accessing updated record values
        • Third party automations
        • Managing automation runs
      • Templates
        • Templates for record values
        • Templates using field values
      • Controlling Access
        • Sharing a base
        • Access control considerations
        • Techniques for limited access
          • Airtable interface
          • Share link access
          • Synced table
          • Prefilled forms + Automations
          • 3rd party portal
          • 3rd party webhook
        • Preventing base duplication
      • Specific Solutions
        • Most recent form submission for ermail
        • Email digest of a person’s records
        • Sum of Multiple Select choices
        • Link to multiple records based on select choices
        • Dropping Lowest Score (top 4 of 5)
        • Identify the position of a record in a linked record field (top 4 of 6)
        • Sequential Numbering of Records
        • Ending a list of rollup items with ", and"
        • Table ID in a formula
        • Latest/earliest date across several fields
        • Associating People with Records
        • First Tuesday of the Previous Month
      • Miscellaneous
        • Web API Calls for External Integrations
        • Getting paid to work with Airtable
        • Hiring a consultant
        • Airtable Communities
        • Making major changes to a base
      • Suggestion for this guide
Kuovonne's Guide to Airtable
/
/
Rollup fields
Share
Explore

Rollup fields

⁠
⁠
Tip Jar
⁠
Thank Kuovonne for creating this content!
⁠
⁠
⁠
⁠
⁠
A rollup field is a combination of a lookup and a formula field. It requires a linked record field and a formula. The formula can use all the same formula functions and operators available in a formula field.

Ensuring that a rollup value is a single value and not an array

Technically a rollup can result in either an array or a single value. However, because arrays can behave unexpectedly in other formulas, it is best to ensure that your rollup is a single value by using an aggregation formula.
To get a single text string, use ARRAYJOIN(values).
To get a single date, use MAX(values) or MIN(values).
To get a single number, use SUM(values), MIN(values), MAX(values), AVERAGE(values), COUNT(values), COUNTA(values), or COUNTALL(values).

Removing blanks or duplicates

Sometimes you want to remove blank values or duplicate values from the rollup:
To remove blank values from a rollup result, nest ARRAYCOMPACT(values) inside an aggregation formula.
To remove duplicates from a rollup result, nest ARRAYUNIQUE(values) inside an aggregation formula.
You can nest these functions together: ARRAYJOIN(ARRAYUNIQUE(ARRAYCOMPACT(value)))

Data type of a rollup

A rollup can have one of three data types.
number. This is the data type if all possible results of the formula are numbers. The configuration screen will have options for formatting the number. In the grid view, numbers are right aligned.
date. This is the data type if all possible results of the formula are dates. The configuration screen will have options for formatting the date.
text. This is the default data type. If any possible result of the formula is text, including an empty text string "", the end result is text. This data type does not have any formatting options. In the grid view, text is left aligned.

Order of items in a rollup

The order of items in a rollup is determined by the order of the linked records in the linked record field. Normally linked records are added at the end of the list of linked records; however, the order can be changed.
Methods for changing the order of linked records:
Manually dragging linked records around in the linked record field.
Batch update extension.
Sort linked records extension.
Scripting and automation scripts.
Note that sorting linked records in their own table has no impact on the order in which records are linked.

Rolling up dates

When rolling up a date field, you can use MAX(values) and MIN(values) to get the earliest or latest date. These formula functions normally only work with numbers and not dates in a formula field. However, they do work in a rollup field.

Common rollup formulas

Making lists

Comma separated list
ARRAYJOIN(values, ", ")
List with each item on its own line
ARRAYJOIN(values, "\n")
Bullet list
IF(
COUNTALL(values),
"- " & ARRAYJOIN(values, "\n- "
)

Checking for duplicates

IF(
COUNTALL(values) = COUNTALL(ARRAYUNIQUE(values)),
"All values are unique.",
"There are duplicate values."
)

Default value

A default value is the value when there are no linked values.
IF(
COUNTALL(values),
"default value",
ARRAYJOIN(values, ",")
)

Override value

An override value is a value in an editable field that is used instead of the calculated value.
IF(
{override field},
{override field},
SUM(values)
)

Things rollups cannot do

Rollups cannot perform different operations on individual values. For example, you cannot add 10 to each number before finding the average. You can usually perform that math in the individual records in the other table and then rollup the formula result.
Rollups cannot easily obtain only the “first” value. It is possible to obtain the “first” value using complex formulas, but is rarely worth it.
A single rollup cannot sometimes be text and sometimes be a number. At best, you can have a number that looks like text.
⁠
⁠
⁠
Thank Kuovonne for creating this content!
⁠
⁠
⁠
⁠
⁠
⁠
⁠
⁠
⁠
⁠
⁠
⁠

Want to print your doc?
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.