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.– doubleunaryJun 1 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. Jun 2 at 10:39