I have two spreadsheets that are related to each other. The delineatedLocations spreadsheet enumerates items and groups them together by the key value in Clipboards.
This means that locations will be grouped together, for example, by key “123” and all show up under the Clipboard 123.
I have a third table named Routes where I want to keep track of the Running Total for Samples in delineated locations. So I tried writing this expression to count how many samples in delineatedLocations correspond to each clipboard:
sum( select(routesV7[Running Total for Samples],delineatedLocationsV7[Order]=clipboardsV6[key]) )
But this expression sums the samples in every single clipboard. Every route has at least one clipboard and I want the “Running Total for Samples” column to for every route to count all the samples in all the clipboards in all the locations for that route, not all of the samples for every single location in the app.
Does my explanation make sense?
I have three separate tables with parent-child relationships and I want the samples in the grandchild table counted properly so that it corresponds to the correct parent and grandparent tables.