Adding Values with No Consistent Number of Rows or Columns

L

LarsAustin

New Member
Joined
Feb 27, 2016
Messages
16
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]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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
 
Upvote 0
.. 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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Update a specific table column, considering the row of the active cell
Replies
8
Views
323
Alex Blakenburg
A
O
  • Solved
VBA Code to Find Duplicate Values in a Range and Add a Number at the End
2
Replies
12
Views
678
OilEconomist
O
K
  • Question Question
Formula in a cell with Xlookups doesn't pick up new rows when added.
Replies
6
Views
289
Peter_SSs
Peter_SSs
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
73
Denny57
D
H
  • Question Question
Positioning active cell when adding table rows.
Replies
3
Views
165
myall_blues
myall_blues

Forum statistics

Threads
1,223,953
Messages
6,175,598
Members
452,658
Latest member
GStorm

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top