Filling Right across Multiple Intervals

[rentb23]

Hello,

I have monthly data points for a 3-year period in one table and would like to automatically summarize the annual sums in a separate table. What formula should go in the first cell (C11) (in the attached) so that when i scroll right, it automatically sums in 12-month intevals?

Many Thanks,

[Special-K]

Here's a horrible way of doing it, but works

in C11
=SUM(INDIRECT(CELL("address",OFFSET($A$1,4,COLUMN()*12-33-1))&":"&CELL("address",OFFSET($A$1,4,COLUMN()*12-33+10))))

[rentb23]

Many thanks Speciak-K

Is there a better more straightforward way of doing it? anyone?

Thanks

[mahju]

Hi

In cell c2 type1.
In cell d2 enter formula

Please Login or Register  to view this content.

Copied to right
This will write year number for each Year sequence

Now for year sales use Sumproduct as:

Please Login or Register  to view this content.

Copied to right

Regards

[Haseeb Avarakkan]

If you have years in the starting month in row 3, here is one way:

C11, then drag across.

=SUM(INDEX(5:5,MATCH(C$10,3:3,0)):INDEX(5:5,MATCH(C$10,3:3,0)+11))

[rentb23]

Thanks Mahju... can you please explain the rationale....what does the COLUMN(A1) mean?

[rentb23]

Thanks Haseeb, can you please explain the rationale? Wha tdoes 5:5 stand for?

[mahju]

Thanks
The function Column(cell reference) returns the Column No. of the cell reference.

There is another function of the same type row(cell reference) which returns the row number.

COLUMN(A1) will return the column number of the cell A1 which is '1'

When the formula is copied to right one cell it changes to Column(B1) which would return '2' and so on
Regards

[rentb23]

Thanks Manju but why do i need it to return the column number of cell A1 which is a blank cell... my numbers start at column C? and when i change the cell reference the numbers get mixed up? I don't understand why column A is being used? why not Column C?

[rentb23]

Also, is there a single formula that i can input in cell C16 (attached) that would render the correct values if i fill right and fill down simulaneously?

[rentb23]

Ah ok, now i get it! Sorry about that. thanks