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. 
- Welcome to Web Applications Stack Exchange.doubleunary– doubleunary2023-06-01 13:08:41 +00:00Commented Jun 1, 2023 at 13:08
Add a comment |
1 Answer
Use scan() and map(). Put this formula in cell D1:
=let( 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 | ||
| 60 | |||
| Sasi | 4 | ||
| Vinod | 25 | ||
| Sarath | 45 | ||
| Ravi | 25 | ||
| Vipin | 20 | ||
| 119 | |||
| Mini | 30 | ||
| Sheethal | 21 | ||
| 51 | |||
| Raju | 25 | ||
| 25 |
- 1Use
map(chooserows(vstack(subtotals, ""), sequence(rows(subtotals), 1, 2)), subtotals,... Please do not ask questions in comments. Post a new question instead.doubleunary– doubleunary2023-06-02 10:39:37 +00:00Commented Jun 2, 2023 at 10:39