I have a problem that I cannot seem to figure out a solution to using Google Sheets. I need to generate a list of partial sums using data that is sorted but the size of the input range is undetermined. The size of the partial sum range is likewise undetermined.
I know how to do this in AppScript. However, I seem to run into issues loading the function and my calculations slow down because of it. So I would like to find a solution that uses formulas.
Here is a sample of the data I am working with: https://docs.google.com/spreadsheets/d/1HCCM2sJ3lwIjLBUIKbaSFyUVKexXOSKkNmxp4bSd0xA/edit?usp=sharing
I included both the original sample input and expected result and what I am trying to work with now, using a list of generated indirect ranges.
Another way to look at the problem would be: I want to group the data but keep the general integrity of the list intact; reduce redundancies in the list.
Does anyone have any ideas for a more complete solution using only Google Sheet formulas?
Try
=sum(indirect(B2))
and drag down.
Edit: One formula solution (to be entered in row 2):
=ArrayFormula(MMult(--IF((transpose(row(indirect("A2:A"&max(--RegexExtract(Filter(B2:B,B2:B<>""),":.*?(\d+)")))))<--RegexExtract(filter(B2:B,B2:B<>""),"(\d+):"))+(transpose(row(indirect("A2:A"&max(--RegexExtract(Filter(B2:B,B2:B<>""),":.*?(\d+)")))))>--RegexExtract(filter(B2:B,B2:B<>""),":.*?(\d+)")),,transpose(indirect("A2:A"&max(--RegexExtract(Filter(B2:B,B2:B<>""),":.*?(\d+)"))))),row(indirect("A2:A"&max(--RegexExtract(Filter(B2:B,B2:B<>""),":.*?(\d+)"))))^0))