Increas rows in sum function when copying across columns

[cmclux]

Hi, I have data across rows in sheet1 where I need to sum data from column B in sheet2. I need to sum 6 rows (6 months of payments) of sheet2 in column B starting from a specific month which is in the first row of sheet1 and also in column A in sheet2. When I copy the formula in sheet1 across the columns I would like the sum function to automatically go to the next month and then again sum 6 rows.

So the sum formula in sheet1 G8 would be: =SUM(Premiums!B25:B30)

The sum formula in sheet1 H8 would be: =SUM(Premiums!B26:B31)

Of course I can do this manually but there must be a way to just copy this and have excel change the references. I tried to get somewhere using INDIRECT and ADDRESS but cannot seem to get it right. I have the following formula in G8:

=SUM(INDIRECT("Premiums!B"&COLUMN(B:B)):INDIRECT("Premiums!B"&COLUMN(J:J)))

THANKS!

 

[Domenic]

Try...

G8, copied across:

=SUM(INDEX(Premiums!$B:$B,25+COLUMNS($G8:G8)-1):INDEX(Premiums!$B:$B,30+COLUMNS($G8:G8)-1))

or

=SUM(OFFSET(Premiums!$B$25,COLUMNS($G8:G8)-1,,6))

Note that OFFSET is a volatile function. Volatile functions can prolong re-calculation times.

 

[cmclux]

Thanks, that works perfectly.

 

[Domenic]

You're welcome! Thanks for the feedback!

 

[cmclux]

No problem. Just have a quick question. I am trying to understand the formula better so that I can apply it in the future. Would it be possible for you to explain quickly how it works?

THANKS!

 

[Domenic]

The COLUMNS function returns the number of columns in an array or reference. So, for example, when the formula is entered in G8, the range is $G8:G8, and COLUMNS returns 1. When the formula is copied to H8, the range becomes $G8:H8, and COLUMNS returns 2, and so on. To see how the complete formula is evaluated, select the cell containing the formula, and click repeatedly on 'Evaluate' (Formulas > Formula Auditing > Evaluate Formula for Excel 2010) to see each step of the evaluation.