cell references summing blocks of 7 cells

**ruperupe** · 06-28-2010, 06:42 PM

Hi Ive been searching round for ages on how to do this. Id like to take a sum of a block of every seven rows in a column so

=SUM(E13:E19)

id like to create a formula and be able to autofill to keep the column the same as E but have it repeat like this -

=SUM(E13:E19)
=SUM(E20:E26)
=SUM(E27:E33)

etc etc

This will help me a lot otherwise I will have to type about 600 cells manually!

Thanks a lot for your help.

Rupert

**daddylonglegs** · 06-28-2010, 06:56 PM

Hello Rupert,

Assuming you want the formulas to start from G1 put this formula in G1 and copy down the column

="=SUM(E"&ROWS(G$1:G1)*7+6&":E"&ROWS(G$1:G1)*7+12&")"

That will give you your required formulas as text.

Now you can convert to real formulas like this:

Select column of formulas,
Edit > Copy
Edit > Paste special > values > OK > ESC
Data > Text to columns > Finish

**ruperupe** · 06-28-2010, 07:04 PM

Thanks Daddylonglegs, I didnt think it would be that complicated !!
If I want to be able to drag out and autofill to the right and not down but still add the same ive tried altering
ROWS to COLUMNS but it doesnt work out is there an easy fix?

**daddylonglegs** · 06-28-2010, 07:17 PM

To do the same in a row (starting at G1 again), yes you can use COLUMNS but you also need to move the $ to in front of the column letter rather than the row number, i.e. in G1 copied across

="=SUM(E"&COLUMNS($G1:G1)*7+6&":E"&COLUMNS($G1:G1)*7+12&")"

Also text to columns won't work for a row of data, so in place of that part you can do this:

Select row of values > Edit > Replace > in "find what" use "=" and in "replace with" also use "=" and then hit "Replace All" button.

The above method gives you formulas which explicitly show which cells are being summed. If you don't care about that you can use a single formula in G1 copied across. You can't see at a glance which cells are being summed but it will give you the same results....

For that use this formula in G1 copied across

=SUM(OFFSET($E13:$E19,(COLUMNS($G1:G1)-1)*7,0))

**ruperupe** · 06-29-2010, 06:57 PM

Hi Daddylonglegs

I put the formulas in and they work great. The problem is when I do the columns version then do a copy and paste special Values I get the formulas I want but come to do the find replace and it tells me there is nothing to find so the formulas sit in the cells until I enter text edit mode in the cell and force each one to update by pressing enter!

Is there a way to force an update of all the cells without having to enter each one individually?

Thanks again

**bortap** · 03-07-2013, 09:47 AM

Sorry to reopen this but I am having a similar problem with ruerupe.
I have a list of data numbers in Column B and would like to add them as follows:
D2 = Sum(B2:B97)
D3 = Sum(B98:B193)
and so on..
I have 35042 numbers to break into 365 blocks.

If I alter the Formula above is this what it should look like?

="=SUM(B"&ROWS(B$2:B2)*96&":B"&ROWS(B$2:B2)*96+96&")"

Any help would be greatly appreciated!!

**arlu1201** · 03-07-2013, 10:23 AM

bortap,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.