Forum Discussion

MarshaBrandes's avatar
MarshaBrandes
Copper Contributor
May 17, 2024

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

  • MarshaBrandes 

    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.

  • dscheikey's avatar
    dscheikey
    Bronze Contributor

    MarshaBrandes 

    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))

     

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP

      dscheikey 

      I'd modify as

      =SUM(A1:INDEX(A:A,XMATCH(TRUE,A1:A100<>"",,-1)))

      in case if blank cells are in the middle.

Resources