Forum Discussion
MarshaBrandes
May 17, 2024Copper Contributor
Sum row until a blank cell is reached
Hello! What formula can I enter to sum a row of data until a blank cell is reached. I know the AutoSum function does this, but I am trying to create a template that has this formula entered for numerous rows of data, not just one row. I appreciate any guidance you can provide!
3 Replies
Sort By
- PeterBartholomew1Silver Contributor
Somewhat more verbose
=BYROW( data, LAMBDA(row, LET( accumulated, SCAN(0, row, LAMBDA(x, y, SUM(x, y))), rowTotal, SUM(row), XLOOKUP(TRUE, ISBLANK(row), accumulated, rowTotal) ) ) )
The formula returns a partial sum for each row of data.
- dscheikeyBronze Contributor
In my example, the list starts in cell A1. The data is analysed up to cell A100 at the most.
=SUM(OFFSET(A1,0,0,XMATCH(TRUE,A1:A100="")-1))
I'd modify as
=SUM(A1:INDEX(A:A,XMATCH(TRUE,A1:A100<>"",,-1)))
in case if blank cells are in the middle.