Grouping rows in columns...

black eyed dog · May 13th, 2002, 05:09 PM

Hello

I was hoping someone could help, I'm sure its pretty simple.

I have a column with 3300 rows and want to sum a group of 5 rows every 30... e.g. SUM(A26:A30) then SUM(A56:60) then SUM(A86:90)... etc. Is there a quick way so I can type in one formula and drag down making it automatically jump 30 each row? Rather than it going (A36:A30), (A37:A31), (A38:32)... etc as it automatically does.

Dog

PaddyD · May 13th, 2002, 05:20 PM

Dog,

You're almost there. You just need a slight variation on what you're currently doing.
Put the sum formula you want in 2 or 3 cells (e.g. at rows 30, 60 & 90) then do the dragging etc. Excel is bright enough to work it out from there...

Paddy

invisigirl · May 13th, 2002, 05:21 PM

Just do the formula twice before you drag it down. For example, if you're summing A1:A5 in cell B5, type in =sum(A1:A5) in that cell, then in cell B10 (for example), type =sum(A6:A10). Then select cells B1:B10, grab the fill handle and drag. It will catch your drift and fill down, summing every five.

Is that what you were looking for?

lars · May 13th, 2002, 05:26 PM

do the three you first mentioned then make sure you highlight 31-90 then drag it down

dimrod · May 13th, 2002, 05:30 PM

If your numbers are in column A, put this in B1 and fill down :-

=IF(MOD(ROW(),30)=0,SUM(INDIRECT("A"&ROW()-4&":A"&ROW())),"")

Bit of a messy formula - there has to be a better one.

You could delete the formulas from column B that return "" by selecting them via Edit>GoTo>Special>Formulas>Text, and then Edit>Delete.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 13th, 2002, 05:09 PM	#1
black eyed dog New Member Join Date: May 2002 Posts: 5	Hello I was hoping someone could help, I'm sure its pretty simple. I have a column with 3300 rows and want to sum a group of 5 rows every 30... e.g. SUM(A26:A30) then SUM(A56:60) then SUM(A86:90)... etc. Is there a quick way so I can type in one formula and drag down making it automatically jump 30 each row? Rather than it going (A36:A30), (A37:A31), (A38:32)... etc as it automatically does. Dog

May 13th, 2002, 05:20 PM	#2
PaddyD MrExcel MVP Join Date: May 2002 Posts: 13,278	Dog, You're almost there. You just need a slight variation on what you're currently doing. Put the sum formula you want in 2 or 3 cells (e.g. at rows 30, 60 & 90) then do the dragging etc. Excel is bright enough to work it out from there... Paddy

May 13th, 2002, 05:21 PM	#3
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	Just do the formula twice before you drag it down. For example, if you're summing A1:A5 in cell B5, type in =sum(A1:A5) in that cell, then in cell B10 (for example), type =sum(A6:A10). Then select cells B1:B10, grab the fill handle and drag. It will catch your drift and fill down, summing every five. Is that what you were looking for?

May 13th, 2002, 05:26 PM	#4
lars Board Regular Join Date: Mar 2002 Location: California Posts: 105	do the three you first mentioned then make sure you highlight 31-90 then drag it down

May 13th, 2002, 05:30 PM	#5
dimrod Join Date: May 2002 Posts: 73	If your numbers are in column A, put this in B1 and fill down :- =IF(MOD(ROW(),30)=0,SUM(INDIRECT("A"&ROW()-4&":A"&ROW())),"") Bit of a messy formula - there has to be a better one. You could delete the formulas from column B that return "" by selecting them via Edit>GoTo>Special>Formulas>Text, and then Edit>Delete.