Adding Values with No Consistent Number of Rows or Columns

[LarsAustin]

Hello,

I am trying to find an efficient formula that will add the values in B4:F4 (yellow cells) and then subtract it from the value in A1 and put it in cell A2. But then I want the same for values in G4:J4 (gold cells) and then subtract it from the value in G1 and put in cell G2. And so on the next.

The pattern is that it will add the values in row 4 but only up to a point where the next adjacent cell in Row 1 is not equal to zero.

I hope clearly explain what I want to achieve.

Thank you.

[TABLE="width: 1056"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]D
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]E
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]F
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]G
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]H
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]I
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]J
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]K
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]L
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]M
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]N
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]O
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]P
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]Q
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]R
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]S
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]T
[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]U
[/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]960
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]360
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]360
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]360
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl75, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]870
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]330
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]240
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]330
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]30
[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]30
[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]30
[/TD]
[TD="class: xl71, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl72, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"]0
[/TD]
[TD="class: xl73, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"]0
[/TD]
[TD="class: xl73, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"]30
[/TD]
[TD="class: xl74, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFE699]#FFE699[/URL] , align: right"]0
[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]30
[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]30
[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]30
[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl71, bgcolor: yellow, align: right"]30
[/TD]
[TD="class: xl76, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: right"]0
[/TD]
[TD="class: xl77, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: right"]0
[/TD]
[TD="class: xl77, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: right"]30
[/TD]
[TD="class: xl78, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: right"]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

Assuming column U is the last column:

A2: =IF(A1>0,A1-SUM(A4:$U4)+SUM(B1:$U1)-SUM(B2:$U2),"") copy to the right

 

[Peter_SSs]

.. or if you cannot just drag the formula to the right (it looks like you might possibly have merged cells in between), you could take the central part of Stephen's formula
=A1-SUM(A4:$U4)+SUM(B1:$U1)-SUM(B2:$U2)
and put it in A2 and then copy to G2, K2, etc

Note that whichever formula you use, the correct results will not appear until you have filled across to the other relevant cells in row 2.