/ How to

How to Use and Enable Rollup Feature in Airtable Database

Airtable is a powerful database software that allows users to organize, collaborate on, and analyze data in a flexible spreadsheet-database hybrid. One of its most useful features for data analysis is the rollup field.

What is a Rollup Field

A rollup field allows you to perform calculations and aggregations on values from records linked in another table. For example, you can sum up sales amounts from a linked “Sales” table to show total sales in a “Customers” table.

Some things a rollup field can do:

  • Sum, average, count, or get other aggregates on values from linked records
  • Concatenate or join text from multiple linked records
  • Apply mathematical formulas using values from linked records
  • Filter which linked records are included based on conditions

When to Use a Rollup Field

Rollup fields are useful when you want to:

  • Summarize data – Add up numbers, concatenate text, etc. from linked child records to show on a parent record
  • Create reports and dashboards – Roll up key metrics in one table to create a reporting table
  • Avoid repetitive manual calculations – Automatically calculate aggregated totals as child records update

For example, rollups enable you to easily show total sales for each customer without manual calculation.

How to Add a Rollup Field

Adding a rollup field only takes a few clicks:

  1. Open the table you want the rollup field to live in
  2. Click the “+” icon to add a new field
  3. Select “Rollup” as the field type
  4. Choose the source table that contains the linked records
  5. Select the field from that table you want to aggregate
  6. Pick an aggregation formula like SUM, COUNT, etc.
  7. Customize filters (optional) to limit included records
  8. Name and create the new rollup field

Rollup Field Formulas

Airtable supports several handy formulas for aggregating linked values in a rollup:

  • SUM – Sums numbers from a field
  • AVERAGE – Averages numbers
  • MEDIAN – Gets median of numbers
  • COUNT – Counts records
  • COUNTUNIQUE – Counts unique values
  • MIN – Minimum number
  • MAX – Maximum number
  • CONCAT – Concatenates text

You can combine these with logical operators like AND/OR as well.

Filtering Rollup Fields

When creating a rollup, you can filter which linked records are included by applying conditions – for example, only summing sales amounts greater than $100.

To add filters:

  1. Check the box for “Only include linked records that meet certain conditions” when configuring your rollup
  2. Add filters based on fields from the linked table
  3. Use conditional operators like =, <, >, contains, starts with, etc. to define your conditions

Examples of Using Rollup Fields

Here are some examples of handy ways to use rollup fields:

Sum Sales by Customer

Table 1: Customers
Table 2: Sales

Create a Rollup field in Customers table that sums the Sale Amount from all linked Sales records

Count Orders by Status

Table 1: Orders 
Table 2: Order Status Updates

Create a Rollup field that counts Order Status linked records where Status = "Completed"

List Product Categories Sold

Table 1: Customers
Table 2: Sales

Create a Rollup field that concatenates distinct Product Category text from linked Sales records

Tips for Using Rollup Fields Effectively

  • Give rollup fields informative names like “Total Sales” or “Completed Orders”
  • Display important rollups on table grids and in summary views
  • Combine rollup fields with record linking to build reporting tables
  • Take care not to create circular references between linked tables
  • Add indexes to optimize performance of large rollup calculations

The rollup field is one of Airtable’s most useful features for summarizing, analyzing, and reporting on linked data across tables. Mastering rollups will take your database to the next level!

Related Posts