fill down with sum of consecutive ranges

[winstonw]

For a range A1:A100, in column D I want to sum(A1:A4), then sum(A5:A8), etc

ColA is quarterly data and I am trying to convert it to annual.

There's a lot of data so I was wondering the formula could be written in ColD to fill down.

 

[jbeaucaire]

Like so:

Excel Workbook
	A	B	C	D
1	1			10
2	2			26
3	3			42
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
Sheet8

 

[winstonw]

Thankyou very much JB. It does what I wanted.

 

[maladroitmattoid]

Can anybody advise how this formula can be modified to do the same for columns? I am trying to do the same for consecutive ranges of 4 columns, and have tried to modify the formula, but can't get it right.

 

[jbeaucaire]

Read up on the OFFSET() function to fully understand it.

=OFFSET(StartingRange, OffsetRow(s), OffsetColumn(s), NumberOfRows, NumberofColumns)

The first version used the 2nd and 4th parameters to do "row" offsets and sizings. You would use the 3rd and 5th parameters instead:

Enter this starting formula in a cell and copy to the right:
=SUM(OFFSET($A$1, ,(COLUMN(A1)*4)-4, , 4))