Related row rollups allow you to perform calculations across Related rows and return aggregate data.

The Related row rollup data type allows you to perform calculations across linked rows in other worksheets and return aggregate data. Unlike Related row lookups, which return raw data from the Related row, Related row rollups can return summaries, or aggregates, of the related data. Related row rollups make it easy to quickly calculate aggregated values of data across multiple worksheets.

Related row rollups work similarly to math functions like SUMIF and COUNTIF, but do not require you to enter any formulas.

To enable Related row rollups, both the Related row and Related row rollup data types must be applied at the column level.

Understanding Related Row Rollups

Let’s say we have two worksheets – a Regions sheet and a Sales sheet – that show different information, but are related via their common “Region” records.

With Related rows, these worksheets are linked and provide easy access to each other’s data, but the data contained in each worksheet remains the same. With a Related row rollup, we can bring aggregate data from one worksheet directly into another.

With a Related row rollup, our Regions sheet can display a sum of the total revenue for all stores within a region, or display the average revenue per store for all stores within a region.

The data that’s brought into our Regions worksheet isn’t a new record, but an aggregate of related records. If we make changes to the underlying related data in our Stores worksheet, the Related row rollup in our Regions worksheet will automatically recalculate and update to reflect the changes.

How to Create and Configure a Related Row Rollup

Related row rollups are created via the Related row rollup data type and can be added to your worksheet like all other data types.

Before creating a Related row rollup, your worksheet must contain a Related row column.

In the example below, we have two worksheets – Regions, our target worksheet, and Stores, our source worksheet – that have already been related via the “Stores” column (Column B) in the target worksheet. The Related row here is a 2-way link.

Right now, we can see the annual revenue of each individual store in our source worksheet, but we want to quickly see aggregated annual revenue information across each entire region. We’ll do this with a Related row rollup column in our target worksheet.

First, select the column that you want to contain the aggregate values. Right-click on your selection and select “Edit data type…” from the dropdown to open the Update column dialog and select “Related row rollup” from the Type dropdown.

You’ll first be prompted to select the column in the related worksheet from where you want to rollup values. Because we’re rolling up the annual revenue, we’ll select “D – 2021 Revenue (USD)”.

Note that Strict is enabled by default and cannot be turned off. From the Rollup using field at the bottom of the dialog, you can select the aggregation function that will be performed on the related data. The Related row data type supports eight types of aggregation functions:

For our rollup, we want to capture the total revenue for each region so we’ll select the SUM function.

You can also click the Formatting tab at the bottom of the dialog to select formatting options for the rollup’s output. Once you’ve configured your rollup column, click the blue Update button in the bottom right corner to save your options and convert your column to a Related row rollup.

Now, Column C of our target worksheet has been populated with the sum of the revenue of all stores within each region using data from the Related rows in our source worksheet. The values that are output are equivalent to those that would be returned by a SUMIF function that searched the source worksheet for matching “Region” values.

The values in the rollup column are non-editable. Changes to the corresponding data in your source worksheet will be reflected in the target worksheet’s rollup column, but the corresponding data in your source worksheet cannot be edited from your target worksheet’s rollup column.

Multiple Related row rollup columns can be added to a single worksheet. For example, we could add a second rollup column displaying the average revenue per store within a region. Or we could add two additional columns displaying the maximum and minimum revenue values found in stores within a region.

Related Row Rollups with 2-way Links and Multiple-Row Linking

When a Related row has been established with a 2-way link, Related row rollups can be created on both sides of the link in the target and source worksheets. Note that when working with a 2-way link, Related row rollups should only be used on both sides of the relationship if both sides of the relationship are linked to multiple rows. If there are not multiple values to aggregate on both sides of the relationship, a Related row lookup on one side of the relationship will usually be a more appropriate data type.

Learn more about working with Related rows in our series of articles on relationships between worksheets. Or, read on to learn more about the rest of Spreadsheet.com's Data Types including Related row lookups.