Step 1: Make sure that your tables are linked

In order to create rollup fields, you first need to link tables. This enables you to connect related data across different tables, allowing for more complex and insightful analyses.

For our example, we will link the records in the Owner field in the Opportunities table with the records of the table Sales Rep.

Step 1.1: Edit the chosen field

Navigate to the "Opportunities" table and find the "Owner" field. Click on the arrow next to the field name to access the field options. Select the "Edit field" option to modify the field settings.

Step 1.2: Edit the field type

In the field settings, choose the "Link to another record" option. This will allow you to connect the "Owner" field to another table.

Step 1.3: Select the "Sales Rep" Table

Choose the "Sales Rep" table from the list of available tables and hit "Save."

Once you've completed these steps, you'll notice that Airtable has automatically created a column called "Opportunities" in the "Sales Rep" table. This column lists each sales representative's opportunities, reflecting the link you've just created.

By linking the tables in this way, you've set the foundation for creating a rollup field that can aggregate data across these connected records. It's a vital step that ensures the integrity and functionality of the rollup field you're about to create.

Step 2: Create a new field

In order to set up a rollup field in Airtable, you now need to create a new field where the rolled-up data will be displayed. In our example, we will use a rollup field to calculate the average number of interactions per opportunity for each sales representative.

Navigate to the table where you want to roll up the data, click on the plus (+) button to add a new field and choose a name for this new column.

Step 3: Choose the “Rollup” field type

Now you'll need to define its type as a rollup field. This tells Airtable that you want to use this field to aggregate data from linked records.

In the list of field types, browse through the options and find the "Rollup" field type.

You can scroll through the list of field types or just click the search bar and type “rollup” to filter the list.

Step 4: Select the source and field to roll up

Now you need to select the source and the specific field to roll up so that you define exactly what data you want to aggregate. Follow the next steps to do so.

Step 4.1: Select the Rollup Source

After choosing the "Rollup" field type, you'll need to select the source by choosing the desired table from the list. In our example, the only table linked to "Sales Rep" is "Opportunities," so Airtable automatically chooses this option.

If Airtable didn’t automatically select the linked table, you’ll have to choose from the dropdown list.

Step 4.2: Choose the Field to Roll Up

Once the source table is selected, you'll need to specify which field from that table you want to roll up. Click on the option to choose a field and select the desired field name from the dropdown list.

By selecting both the source and the specific field to roll up, you've provided Airtable with the necessary information to aggregate the data correctly.

Pro tip: If you want to include only records that meet certain criteria, you'll need to activate that option and set the conditions. This allows you to filter the data that gets rolled up, providing more precise results.

Step 5: Choose a formula

Airtable will prompt you to write the aggregation formula. Once you start writing, a list of formulas will appear for you to select. Here's a breakdown of some commonly used formulas and what each does:

• AND: evaluates if all given conditions are true, returning a true or false result;
• ARRAYCOMPACT: cleans the array by removing any empty or null values, preserving only meaningful data;
• ARRAYJOIN: combines all elements of an array into a single string, separated by commas;
• ARRAYUNIQUE: filters the array to include only unique values, removing any duplicates;
• AVERAGE: calculates the arithmetic mean of a set of numbers, providing an average value;
• CONCATENATE: merges multiple text values into one continuous string;
• COUNT: tallies the number of non-empty numeric values within a set;
• COUNTA: counts all non-empty values, regardless of whether they are numeric or text;
• COUNTALL: provides a total count of linked records, including empty ones;
• MAX: identifies the largest numerical value within a given set;
• MIN: finds the smallest numerical value within a given set;
• OR: returns true if at least one of the given conditions is true;
• SUM: adds together numerical values to provide a total sum;
• XOR: returns true if an odd number of conditions are true, otherwise false.

Step 6: Click “Create field”

Once you've confirmed all the settings, look for the "Create Field" button located at the bottom of the dialog box or settings panel. Clicking this button will finalize the creation of the rollup field, adding it to your table.

Step 7: You’ve created the rollup field

With the rollup field now created, you can see it in action within your table, dynamically calculating the values based on the linked records and the formula you've chosen. In our example, it will calculate the average number of interactions per opportunity for each sales representative, providing valuable insights into their performance.

‍