A formula that autofills by trend, not linear

A

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

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
 
A

Arlen

Thanks, Biff!

Perfect

T. Valko said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top