SUM Until Another Cell is 0

[WeeRab]

Hi everyone,

I'm trying to add cells between a range but I not sure how I can do this? Attached is a screenshot of the table I am working on.

I need to create a formula that will add values from row 6 until there is a value in row 8. For example, the orange cell is equal to the red cell, minus the sum of the grey cells, and the same with the green, purple and blue cells. But to do this I had to manually adjust the equation. I need to find a dynamic equation that changes depending on the values that populate the table.

I'm relatively new to using excel in this way, so any help would be appreciated

 

[6StringJazzer]

Put this formula in C10 and fill to the right:

=IF(C8=0,0,C8-SUM(OFFSET(C6,0,0,1,MATCH(TRUE,INDEX(D8:$I8<>0,),0))))

$scratch.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3
4
5
6			30	20	5	8	0	2	0
7
8			55	0	50	0	0	0	50
9
10			5	0	35	0	0	0	50
Sheet5

Cell Formulas
Range	Formula
C10:H10	C10	=IF(C8=0,0,C8-SUM(OFFSET(C6,0,0,1,MATCH(TRUE,INDEX(D8:$I8<>0,),0))))
I10	I10	=IF(I8=0,0,I8-SUM(OFFSET(I6,0,0,1,MATCH(TRUE,INDEX($I8:J8<>0,),0))))

 

[WeeRab]

Put this formula in C10 and fill to the right:

=IF(C8=0,0,C8-SUM(OFFSET(C6,0,0,1,MATCH(TRUE,INDEX(D8:$I8<>0,),0))))

$scratch.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3
4
5
6			30	20	5	8	0	2	0
7
8			55	0	50	0	0	0	50
9
10			5	0	35	0	0	0	50
Sheet5

Cell Formulas
Range	Formula
C10:H10	C10	=IF(C8=0,0,C8-SUM(OFFSET(C6,0,0,1,MATCH(TRUE,INDEX(D8:$I8<>0,),0))))
I10	I10	=IF(I8=0,0,I8-SUM(OFFSET(I6,0,0,1,MATCH(TRUE,INDEX($I8:J8<>0,),0))))

Thank you so much!

 

[6StringJazzer]

You're welcome!