Excel 2007 : Repeating an Array

**Ryan M** · 05-10-2011, 11:50 AM

I have a formula but cannot figure out how to have it repeat easily.

=SUM('Overview of Hours 2011 Nametop'!C5:C35)
=SUM('Overview of Hours 2011 Nametop'!D5:D35)
=SUM('Overview of Hours 2011 Nametop'!E5:E35)
that goes across to column BJ

now i need to repeat the formula starting 52 cells away like
=SUM('Overview of Hours 2011 Nametop'!C57:C87)
=SUM('Overview of Hours 2011 Nametop'!D57:D87)
=SUM('Overview of Hours 2011 Nametop'!E57:E87)

I've been trying different functions like Offset and index, but havent been able to figure it out

**NBVC** · 05-10-2011, 01:02 PM

Try this formula:

Please Login or Register  to view this content.

adjust the bottom of the ranges $1000 to last row in your whole database.

Then copy across and then down...

**Ryan M** · 05-10-2011, 01:24 PM

Great. How can I transpose it?

**NBVC** · 05-10-2011, 01:51 PM

Try:

Please Login or Register  to view this content.

copied down and across

**Ryan M** · 05-10-2011, 02:00 PM

you are incredible....

I get c5:C607 (which i changed from 1000) is my range. This i have no idea: 1+((ROW(A1)-1)*52), i get that : is the next part of the series, the 1 + and the 31+ are incremental range increases.....can you maybe explain it a little more?

**NBVC** · 05-10-2011, 02:07 PM

If you use the Evaluate Formula feature in the Formulas tab, you can step through the formula to see how it gets to the point of calculation....it would be easier to understand and follow that way....

The 1+((ROW(A1)-1)*52) is basically like a Step in a vba macro.. is "looping" through the range and stepping by 52 cells...

The 31+((ROW(A1)-1)*52 is the size of the range to sum (it captures the last cell in the group range to include in the sum (i.e. 31 cells offset from the next step of 52 cells....