2

This question which specifically referred to summing a column of numbers until a break and then subtotalling on the next following line was answered very helpfully by this answer.

Supplementary to that, I would like to sum a column until a break occurs and place the subtotal in the cell immediately right of the column on the same line as the last value but then to continue the accumulative count to the end of the range.

The scenario is that there are two columns (one for 12 monthly payments simply) and the other for 13 monthly payments in the same 12 month period (one every four weeks with a fluid start date) which means that one month in the year is duplicated and thus the 12 monthly duplicated column should remain blank. (I've changed the background colour of the extra cell simply to highlight it).

The attached spreadsheet provides an example of what I am trying to achieve.

https://docs.google.com/spreadsheets/d/1jqhorLHNSQ9TxVWK0XuYUYC3WalDlcM-aFwmB3zg3p8/edit#gid=209239107

Column H shows the result of the answer already provided and Column I the result of the modified code provided which resets the accumulator after the first blank line.

What I would like to see is the running total as per the LAMBDA formula in column D (contained in cell D3) but for the remaining cells in the example from D9 downwards (or where ever the next break occurs) to remain blank if there is no value in the corresponding line in column C.

Column I is what I have managed to achieve so far but this does not accumulate the running total.

I'm certain there is a simple tweak to the code behind cell I1 but this has so far defeated me.

1
  • 1
    Consider showing your manually entered desired results. Jun 2, 2023 at 18:14

1 Answer 1

2

Wrap the scan() in arrayformula(if()), like this:

=arrayformula( 
  if( 
    sequence(rows(C3:C15)) <= xmatch("?*", to_text(C3:C15), 2, -1), 
    scan(0, C3:C15, lambda(a, c, a + c)), 
    iferror(ø) 
  ) 
)

Your Answer

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

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