Add a constant number to the cell row references of a SUM function.

[greenfortyguy]

Hello,

I am looking for a way to increase the cell row number of a SUM function by 4 as I copy the formula.

The SUM is across two sheets.

The formula in my first cell (G7, on a sheet called '60 min') is:

=SUM('15 min'!G7:G10)

I want the formula to copy into G8 on the '60 min' sheet as:

=SUM('15 min'!G11:G14)

And into G9 on the '60 min' sheet as:

=SUM('15 min'!G15:G18)

I imagine it is something to do with the OFFSET function but I haven't had any luck with this.

Any help would be much appreciate, thanks.

 

[JackDanIce]

Welcome to the board. I think INDIRECT may work better, e.g.

=SUM(INDIRECT("'15 Min'!G"&row()+4*(row()-7) & ":G" & row()+4*(row()-7)+3&")")

Untested

 

[AlanY]

try this in G7 and copy down

=SUM(OFFSET('15 min'!$G$7,(ROW('15 min'!G7)-ROW('15 min'!$G$7))*4,0,4,1))

 

[taurean]

Welcome to MrExcel Forums.

Your idea is correct. You can implement OFFSET like below in cell G7:
=SUM(OFFSET('15 min'!$G$7:$G$10,(ROW()-7)*4,0))
And then copy down

Another solution would be:
=SUM(INDEX('15 min'!G:G,7+(ROW()-7)*4):INDEX('15 min'!G:G,10+(ROW()-7)*4))

 

[greenfortyguy]

Hi all,

Thank you for these answer so far, and for getting back to me so quickly.

Unfortunately, I have realised I also need to be able to copy this formula into the adjacent columns, such that the cell H7 reads:

=SUM('15 min'!H7:H10)

and the cell H8 would read

=SUM('15 min'!H7:H10)

etc.

I realise I can copy these original formulas into each column of row 7 and change the letter in dollar signs, but wondered if there is perhaps a formula that can do both at once?

Thanks again,

 

[greenfortyguy]

I have changed taurean's suggestion from

=SUM(OFFSET('15 min'!$G$7:$G$10,(ROW()-7)*4,0))

to

=SUM(OFFSET('15 min'!G$7:G$10,(ROW()-7)*4,0))

This seems to have done the trick?