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
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.
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
The much awaited “Related Row Rollup” feature is now available in the product with the latest upgrade.
For Additional information go through the below link
4 Likes
So awesome! I’m going to be experimenting with this right away. I have a construction schedule where I want to list all of the material costs for specific steps. I was going to add the materials into the Gantt chart as separate task items, but they really aren’t separate tasks (other than the ordering and purchase of those items). But now I’m hoping to have a separate worksheet that acts as a price list and perhaps a junction table for listing the quantities of materials needed for specific tasks, and then use a rollup field in the main Gantt chart for calculating the cost associated with that task.
Edit: Rollups are awesome! Hoping to make the construction schedule example available soon.
1 Like