Excel formula to sum row until blanks in columns

0
votes

I am looking to sum set of cells in row based on other row value or until blank column is found in same row. Please refer below table with example.

                A   B   C   D   E   F   G   H   I   J
Values for Sum  1       1   1       2   3   1       1
Helper          1   0   0   1   0   0   0   1   0   1
Output required 1           2               6       1

I need formula in last row(Output). Formula should be able to sum until blank column in first row or until 1 in helper row.

Example:

  • In A1, value is 1 so the output in A3 is 1.
  • In H2 when value is 1 that is helper row, then in H3 it should sum F1+G1+H1 = 6.

Formula to sum can either use helper row or blanks in first row.

Hope there is formula to get this result.

1
sumexcel-2007excel-formula

1 Answers

0
votes

If you are comfortable with sum appearing at the start of the break, you can try this formula:

=IF(A2="..",SUM(OFFSET(B2,,,,(MATCH("..",B2:$Y$2,0)))),"")

you will not be needing helper row for this formula!! Press Ctrl+Shift+Enter to execute

enter image description here Your data in A1:Y3