Formula, lookup, count, and rollup fields (columns) are powerful field types that allow you to compute a value based on values in other cells.
Note that these computed field types are slightly different from formulas in a spreadsheet. In a spreadsheet, you can put a formula in any cell, and have it reference any other cell in the sheet. In Airtable, you configure computed fields that apply the same formula to every record in the table. As such, these formulas reference other fields rather than other cells so that they apply to every record.
Note that the rollup, lookup, and count fields can only be used when you have a record link field in your table.
To see some real-life examples of how a nonprofit uses formula, lookup, count, and rollup fields to run their organization, head on over to our blog.
Each computed type is described in more detail below.
Formulas allow you to compute a value in each record based on other cells in that same record. For example, if you have a table of product orders, you can create a field that computes the Total Cost for each record based on the Price and Quantity.
To configure a formula, select the "Formula" type from the field configuration menu. From there, you can enter the formula into the text box as you would in a spreadsheet, except you use column names instead of cell ranges. A formula can be composed of functions (e.g. SUM, CONCATENATE), operators (e.g. +, -, *, /, &), and other columns from the same table.
When you start entering your formula, you will see suggestions of columns and functions you can use in the formula.
If your formula will result in a numerical output, you have the option of formatting the formula's result as a decimal, an integer, a currency, or a percent, and of selecting the precision (for decimals, currencies, or percentages).
For a complete reference of formula functions and operators, please refer to the Formula Field Reference.
A lookup field allows you to look up a specific field in a linked record.
For example, if you have an "Orders" table with a field linking to "Clients", you could create a lookup field that displays the linked client's address.
Lookup configuration is pretty straightforward. First, you pick the field with the linked records you want to look up. Then, you pick the cell on those linked records that you want to display.
Note that if there are multiple linked records, the lookup will concatenate the cell values and separate them with a comma.
A count field lets you count the number of linked records in a record link column. To configure a count column, you simply choose the column with the linked records that you want to count.
A rollup field performs calculations or formulas on specific cells from records that are linked to another field in your table. Sound complicated? It’s not! Watch this video guide and get started in seconds. Spreadsheet users can think of the rollup field as a way of creating more sophisticated pivot tables.
A rollup field lets you aggregate values from linked records. For example, consider a database that helps the owner of a furniture store keep track of orders and pieces of furniture. A single order could contain several pieces of furniture. This could be represented by linking several furniture records to each order. A rollup would then allow you to calculate the total price of the furniture records in each order.
You configure a rollup by first selecting the field that links to the records you're interested in aggregating.
You then select the cell on those records that you'd like to aggregate.
The last step is to select a way to aggregate the cell values from the linked records. Average, sum, min and max are a few examples of aggregation functions you can use on a rollup.
When you start editing the aggregation input, you will see suggestions for potential rollup functions with a brief description of each function.
For a complete reference of rollup aggregation functions, please see our rollup field reference.
Note that a rollup field will roll up data from all records in the target linked table, regardless of whether or not those records have been filtered in some way. If you only want to rollup certain linked records in each cell, you will need to use IF formulas to make conditional rollups.