A formula that autofills by trend, not linear

[Arlen]

I have groups of months on one sheet, and a yearly summary sheet besides.

I need the first yearly formula to sum the range Months!A1:12.
I need the second year to sum the range Months!A13:A24.

As I autofill this formula down, I need the references to jump by 12, but
Excel is only counting up by 1. How do I set Excel straight on this one?

Thank you for your benevolence.

 

[T. Valko]

Assume you enter the first formula in cell A1.

=SUM(OFFSET(Months!A$1,(ROWS(A$1:A1)-1)*12,,12))

Copy down as needed.

A1 will be the sum of Months!A1:A12
A2 will be the sum of Months!A13:A24
A3 will be the sum of Months!A25:A36
A4 will be the sum of Months!A37:A48
etc
etc

 

[Arlen]

Thanks, Biff!

Perfect

Assume you enter the first formula in cell A1.

=SUM(OFFSET(Months!A$1,(ROWS(A$1:A1)-1)*12,,12))

Copy down as needed.

A1 will be the sum of Months!A1:A12
A2 will be the sum of Months!A13:A24
A3 will be the sum of Months!A25:A36
A4 will be the sum of Months!A37:A48
etc
etc

 

[T. Valko]

You're welcome. Thanks for the feedback!