I want to sum the cells until I get a blank cell in the column B then add that sum in next column. In the below pic sum of C2:C4 should come in D5,sum of C6:C10 in D11 and so on. Database is so large. So filling formula in every column is not possible. Please find solution to this issue. enter image description here


1 Answer 1


Use scan() and map(). Put this formula in cell D1:

  range, C2:C, 
  numRows, arrayformula(xmatch("?*", to_text(range), 2, -1)), 
  data, array_constrain(range, numRows, 1), 
  blank, iferror(ø), 
  subtotals, scan(0, data, lambda(a, c, if(c, a + c, blank))), 
  lastInGroup, map(vstack(subtotals, ""), vstack("", subtotals), lambda(x, y, if(x = "", y, blank))), 
  vstack("Subtotals", lastInGroup) 

The results look like this:

Name Amount Subtotals
Kamla 10
Sruthi 20
Sarath 30
Sasi 4
Vinod 25
Sarath 45
Ravi 25
Vipin 20
Mini 30
Sheethal 21
Raju 25

See scan() and map().

  • 1
    Use map(chooserows(vstack(subtotals, ""), sequence(rows(subtotals), 1, 2)), subtotals,... Please do not ask questions in comments. Post a new question instead. Jun 2, 2023 at 10:39

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.