How to calculate a subtotal based on multiple columns in Google Sheets

Question

I want to create a dynamic formula, which will calculate the sum of column C, subtracted by the sum of column D, between the range of the current new subtotal and the previous subtotal, and adding the previous subtotal to it. If I were to add value on the next row in column c of 500, and the next row has the value of 600 in D, if the next row is blank, the new subtotal in B should be: (600+(500-600)=) 500

However, I am having trouble determining the ranges for the sums, I don't know how to define the range between the new subtotal and the previous one. I've only been able to create a formula to recognise /when/ to calculate the new subtotal:

=IF(AND(ISBLANK(C113),ISBLANK(D113),NOT(ISBLANK(C112)),NOT(ISBLANK(D112))), "newsubtotal", "")

Can anyone help me with this? I haven't been able to find a working solution for this problem as of yet.

Answer 1

• sequence can be generated like this by pasting it into B1 and dragging it down:

  =SUM(INDIRECT("C1:C"&ROW()))-SUM(INDIRECT("D1:D"&ROW()))

  

• to jump over one row you can do this:

  =IF(OR(C1<>"", D1<>""), , SUM(INDIRECT("C1:C"&ROW()))-SUM(INDIRECT("D1:D"&ROW())))