Sum varible range

stapuff

Well-known Member
I have the following:

A2 = 10
B2 = 10
C2 = 10
D2 = 10
E2 = 10

F2 is =sum(A2:E2) result is 50.

If I insert another column between E & F. The formula moves over and stay's the same range.

I am looking for a formula that sum's the 5 columns to the left of the cell with the formula even if a new column is added. G2 would now contain =sum(B2:F2)

Thanks Kurt

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
I have always included a blank column in the past, but you made me think.
try this, it worked for me.
I put this in F2
=SUM(A2:OFFSET(F2,0,-1))

G2:

=SUM(F2:INDEX(A2:F2,1,COLUMN(F2)-5+1))

or:

=SUM(F2:OFFSET(F2,0,-5+1))

Your 2nd formula was exactly what I needed.

Again - Outstanding!

Thanks,

Kurt

Replies
8
Views
195
Replies
3
Views
244
Replies
3
Views
381
Replies
10
Views
188
Replies
8
Views
380

1,219,574
Messages
6,149,082
Members
450,855
Latest member
onecodevee01

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.

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

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