A formula that autofills by trend, not linear
 

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.

A formula that autofills by trend, not linear
 

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

--
Biff
Microsoft Excel MVP


"Arlen" wrote in message
...
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.

A formula that autofills by trend, not linear
 

Thanks, Biff!

Perfect

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Arlen" wrote in message
...
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.

A formula that autofills by trend, not linear
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Arlen" wrote in message
...
Thanks, Biff!

Perfect

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Arlen" wrote in message
...
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.