How to change drag increments.

I am doing a simple summation task for 70 years of data. Each year has 12 things that need to be added together.

Is there a way I can write something like "= sum(B2:B13)", and then drag down so the boxes below look like:

"= sum(B2:B13)"
"= sum(B14:B25)"
"= sum(B26:B37)"
...

when I try this, excel increases every "B" by 1, I need to to increase the "b's" by 12.

Thanks

You can use INDEX

First formula

=SUM(INDEX(B:B,2+(12*(ROWS(A$1:A1)-1))):INDEX(B:B,1+(12*ROWS(A$1:A1))))
copied down

You can also use OFFSET but this is Volatile (see link in sig. for more info)

=OFFSET($B$2,(12*(ROWS(A$1:A1)-1)),,12,1)
copied down

change the references to A$1 to be that of the cell which contains the first formula.

The INDEX is used to create a range, ie

INDEX:INDEX

which can in turn be worked with like any other range, ie

=SUM(INDEX(A:A,1):INDEX(A:A,10)) is akin to SUM(A1:A10)

In your case the starting row of the range is determined by number of formulae processed thus far * 12 plus 2 (first row of results to be summed)

So for first formula you do not have any prior calcs so

INDEX(B:B,2+(12*(# of prior calcs)))

In the first calc prior calcs should be = 0 thus 2 + 0 thus B2

In the next row below prior calc will become 1 thus 2 + (12*1) thus B14

The closing row is determined by 1 + number of calcs up to and including current * 12, so for first formula

INDEX(B:B,1+(12*# of calcs incl.))

In the first calc this should result in 13 -> 1 + (12*1) -> thus B13

In the next row the # of calcs increments and therefore -> 1 + (12*2) -> B25

The count of calculations is conducted using the ROWS( ) function with a mixture of absolute & relative referencing in the range used within - see XL Help for more info. on both these subjects.