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

Columns 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.

1 Answer 1

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



  • to jump over one row you can do this:

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



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.