Conditionally accumulate rows

Question

I have tabulatory data as follows

2020-01-01 | yes | 5
2020-01-01 | no  | 3
2020-01-02 | no  | 2
2020-01-03 | yes | 7
2020-01-10 | no  | 4
2020-01-11 | yes | 9
2020-01-12 | yes | 22

and I want to create a new "collapsed" or "accumulated" table from this.

The output should look as follows

           - 2020-01-01 | 5   //5
2020-01-01 - 2020-01-03 | 12  //3+2+7
2020-01-03 - 2020-01-11 | 13  //4+9
2020-01-11 - 2020-01-12 | 22  //22

I basically want to add all the values of rows with no to the next row with yes. And add the timeframe between these accumulated yes rows as a first column.

I'm not so experienced with spreadsheet calculations. Is this possible and if yes how?

I would prefer to have a solution that works in libre office calc, but I do also have access to microsoft excel.

Answer 1

Here's a possible solution (using Excel):

• start with your basic data in A1:C8
  
• add 4 columns: one to control when to subtotal, one for the start date, one for the end date, and one for the actual subtotal itself, using the formulas shown ((sorry, just noticed I used y/n instead of yes/no, you'll have to adjust the formulas appropriately):

• With a cell somewhere in the table selected, hit the "subtotal" button (in the Data ribbon, "outline" section)
• in the Subtotal dialog, set it for At Each Change in ST_Level, Use Function Max, Add Subtotal To StartDate, EndDate, Subtotal; click OK.
  
• collapse the resulting table to level 2 using the "grouping" controls that will appear on the left.
  

That gets you exactly the data you want, plus the extra "grand max" row and a few blank columns. If you want to reduce this to exactly and only the data you want, you can then:

• select all of the relevant data cells (excluding the "Grand Max" line)
• press F5
• click "Special..."
• select "Visible cells only"
• click OK
• Ctrl-C to copy the selected cells
• paste them in a new sheet
• Format the results to your liking.