Related Row Rollups allow you to perform calculations across linked rows in other worksheets and return aggregate data. Similar to Related Row Lookups, Related row rollups look up the values of a cell already in a Related row, and require at least one Related row column to exist in the worksheet. Related row rollups can only be applied at the column level. 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 are a great way to quickly calculate aggregate data across multiple worksheets. Perhaps you’re building out a content calendar and want to reference the total number of articles to be written about each topic. Or maybe you’re tracking multiple accounts in your stock portfolio and want to find the average return in each one. Or maybe, as in the example below, you’re working in a job applicant tracking workbook and want to find the total amount of salaries being paid out for job openings at each office location.
Setting a Column to the Related Row Rollup Data Type
To set up a Related row rollup between two worksheets, you must specify the data type for a column as Related row rollup .
To do so, select a column and right click on your selection to open the dropdown menu. From the dropdown, select Edit data type… and select Related Row Rollup from the list of data types to open the Update column dialog.
Within the Update column dialog you can choose the worksheet to relate and the column to rollup, similar to selecting the related data in a Related row.
In the example above, we are using the Related row rollup to generate the sum of salaries expected to be paid out for all job openings in a given location. The Related row rollup is relating the salary information for column “G - Job Openings” from the target worksheet “Job Openings” and automatically summing the related values.
In setting up your related row rollup column, you can choose how to aggregate your related data from the Update column dialog. The related row data type supports eight types of aggregation functions:
Returns the sum of the related values
|AVERAGE||Returns the average of the related values|
|COUNT||Returns a count of the related values|
|MEDIAN||Returns the median of the related values|
|MIN||Returns the minimum of the related values|
|MAX||Returns the maximum of the related values|
|UNIQUE||Returns a comma-separated list of the unique values among the related values|
|CONCATENATE||Combines the list of related values into one comma-separated text string|
In the example below, we are changing the column "H – Budgeted Salary" from a Sum of the related values to the Average of the related values, giving us the average salary of all of the job openings in a specific location.
Once you’ve applied the rollup formula, you can format the resulting data within the Update column dialog as well. By default, the column is set as Auto detect, which automatically formats the data based on the inputs. For example, if your rollup formula takes two column values with currencies as inputs, the resulting data will be formatted as currency as well.
If the auto detection returns an incorrect format or if you’d like your rollup formula data to return a different format, you can manually select how the data appears. Within the Update column dialog, click on the Formatting tab next to Rollup and select your desired data format. In addition to manually selecting the data type, the Formatting tab also allows you to apply specific styles to the resulting data.
Related row rollups are always Strict. You cannot enter data into these cells, only populate them with rollups from related rows. To change the data that appears in a lookup, you must edit the data in the related worksheet.