Formula with Range set at regular intervals

**jayheron1988** · 03-23-2010, 09:54 PM

Hi all, first post.
If this has already been asked, please send me a link but I have searched and found nothing.

I want to create a formula so that when I drag it down to apply to multiple cells the range in the formula increases at say an interval of 5 rather than 1. So it continues from where the previous cell finished.

For example, if in cell A1 I had:
=SUM(B1:B5)

I'd want cell A2 to have:
=SUM(B6:B10)

Rather than:
=SUM(B2:B6)

I hope that illustrates what I'm after as I can't think of a better way to word it!

Thanks in adance for any help!

**Palmetto** · 03-23-2010, 10:10 PM

This may be possible without the use of helper cells, but here is one way to go about it.

Assuming values are in column-A, column-B will hold the sum formula and column-C & D are helpers:

B1: =SUM(INDIRECT("A"&C1&":"&"A"&D1))
C1: =ROW()
D1: =ROW()+4

Then in C2 and D2 copied down
C2: =C1+5
D2: =D1+5