Rollup Data Type - SUM / AVERAGE / MAX etc

Is there, or will there, be a way to use a related row lookup to return the SUM, AVERAGE, MAX, or MIN of values when the related row allows linking to multiple rows. Currently, the related row lookup returns the value from just the first match.

In Airtable, they call this type of field a “Rollup” and they let you choose from a variety of aggregation functions.

In Excel, a formula to do a SUMIF with “or” type matches, meaning that I want the sum of all values where the row matches “lookup value 1” OR “lookup value 2”, would be:
=SUMPRODUCT(SUMIF(lookup_range,{“lookup value 1”,“lookup value 2”},sum_range))
I haven’t been able to get this to work in spreadsheet.com

Here is a Use Case
Table 1: Tasks
Table 2: Resources
I want to assign multiple resources to a specific Task.
The Resources table lists the cost for each resource.
My Rollup field is added to the Tasks table so that I can calculate the sum of the costs of all the assigned resources.

1 Like
  1. Support for Excel’s {} Array Formulas is planned. Tracked as Issue : 001560
  2. Rollup Type field is something we considered in the Roadmap. Let me get back on this.

However, you can work this way.

Table1: Feature ( has Tasks )

Table2: Tasks ( has Feature , Hours )

You can sum “Task Total Hours” in Table1 ( Feature ) via

=SUMIF(Tasks!B$2:B$100,A2 ,Tasks!C$2:C$100 ) // Where A2 reads “My Feature”

This assumes Table1 Feature names are unique.

any updates on this.

would love to be able to do a rollup function like in Airtable

would be nice to be able to take what’s in the Total Package Column, and add the array of values in the Total Order Amount Column. Is there currently a way to do this? or is this part of this thread, and moreso something coming down the line with the work with Array functionality? Thanks in advance for any advice.
2021-02-19 15.15.19 app.spreadsheet.com 373602662bf8

2 Likes

We are looking at this thread. Will get back to you on Monday.

Benjamin’s example is a good one and likely a common need.

1 Like

Benjamin, We are currently spec-ing and designing the “Related row rollup” datatype. We expect to roll it out in the 3-5 month time horizon.

4 Likes

Can’t wait to see this feature.

1 Like