Adding Cells relative to one another

ds_robb

Board Regular
Joined
Aug 25, 2002
Messages
72
I am looking to add cells together relative to one another. So for example I have a line of data say from cells A1 to A52 and for simplicity of explanation, lets say A1 = 1, A2 = 2, A3= 3 etc all the way up to A52. What I am trying to do on the line below is to sum these into weekly amounts. So I would sum (A1:A7) and the result would go in C1. I then want to sum (A8:A14) to go into C2 and (A15:A21) into C3 and so on. If I copy the calculation from C1 to C2 the cells are relative, so (A1:A7) would become (A2:A8) rather than as I want it to be (A8:A14), i.e. go up by 7 Can you copy a sum/formula to the next cell increasing the range you want to sum by 7

Hope this makes sense.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Could you use a pivot table and group your data in groups of 7?
 
Upvote 0
How about
=SUM(INDEX($A$1:$A$24,ROW()*7-6):INDEX($A$1:$A$24,ROW()*7))
 
Upvote 0
How about
=SUM(INDEX($A$1:$A$24,ROW()*7-6):INDEX($A$1:$A$24,ROW()*7))

Thank you, that is getting there, but I was in error in my explanation as I used the wrong cell references. The data is on row 1 going from columns A1 to AZ1, so I should of said A1= 1, B1=2, C1=3 all the way up to AZ = 52 So I would sum (A1:G1) and the put the result in C1. I then want to sum (H1:N1) to go into C2 and (O1:U1) into C3 and so on. If I copy the calculation from C1 to C2 the cells are relative, so (A1:G1) would become (H1:N1) rather than as I want it to be (B1:H1), i.e. go across by7 Can you copy a sum/formula to the next cell increasing the range you want to sum by 7.

I presume you would replace the row with column above, but my knowledge is limited on this and it's foxing me a little.

Many thanks
 
Upvote 0
Thank you, that is getting there, but I was in error in my explanation as I used the wrong cell references. The data is on row 1 going from columns A1 to AZ1, so I should of said A1= 1, B1=2, C1=3 all the way up to AZ = 52 So I would sum (A1:G1) and the put the result in C1. I then want to sum (H1:N1) to go into C2 and (O1:U1) into C3 and so on. If I copy the calculation from C1 to C2 the cells are relative, so (A1:G1) would become (H1:N1) rather than as I want it to be (B1:H1), i.e. go across by7 Can you copy a sum/formula to the next cell increasing the range you want to sum by 7.

I presume you would replace the row with column above, but my knowledge is limited on this and it's foxing me a little.

Many thanks
Actually, I think I have it now : =SUM(INDEX($A$1:$AY$1,COLUMN()*7-6):INDEX($A$1:$AY$1,COLUMN()*7))

Thank you so much for you help
 
Upvote 0
Glad you sorted it & thanks for the feedback
 
Upvote 0
The data is on row 1 going from columns A1 to AZ1, so I should of said A1= 1, B1=2, C1=3 all the way up to AZ = 52 So I would sum (A1:G1) and the put the result in C1.
Looks like you are still a bit mixed up with your references. If the data is in A1 to AZ1 then that will include C1, so the result can hardly go in that cell. ;)

Also a discrepancy between your description of going to column AZ and your formula below that only goes to column AY. :confused:


Actually, I think I have it now : =SUM(INDEX($A$1:$AY$1,COLUMN()*7-6):INDEX($A$1:$AY$1,COLUMN()*7))
I would suggest a slight change. I'm assuming that first formula is actually in cell A2 (or somewhere in column A at least)
My suggestion would be to put this formula in the next cell below yours in column A and copy across a few columns.
=SUM(INDEX($A$1:$AY$1,COLUMNS($A:A)*7-6):INDEX($A$1:$AY$1,COLUMNS($A:A)*7))
You should see the same results in both rows.

Now go to column A and insert a new column A in your worksheet and look what happens to your formula results and what happens to mine.
The change I suggested just makes your worksheet a bit more robust in case somebody does decide to add any columns at a later time.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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