Dragging formulas across columns while incrementing rows

**gautamacharya** · 10-12-2011, 02:59 PM

Hi I have a data in columns A:D rows 1 to 100.
I want:

A101= SUM(A1:A7)
B101= SUM(B2:B8)
C101= SUM(C3:C9)
D101= SUM(D4:D10)

Thus incrementing the row value by 1. Basically when i drag the formula in A101 i want this incrementing to happen.

Thanks
Gautam

**GeneralDisarray** · 10-12-2011, 03:14 PM

Please Login or Register  to view this content.

paste and drag accross...worked for me

**NBVC** · 10-12-2011, 03:23 PM

Try:

=SUM(INDEX(A$1:A$40,1+COLUMNS($A$1:A$1)-COLUMNS($A1)):INDEX(A$1:A$40,7+COLUMNS($A$1:A$1)-COLUMNS($A1)))

adjust the A1:A40 range to suit height of the whole range from A1 to last column in D....

and copy across.

**TMS** · 10-12-2011, 03:38 PM

This seems to work, but it's not very pretty.

A101 and drag across:

=SUM(OFFSET($A$1,ROW(A101)+(COLUMN(A1)-1)-101,COLUMN(A1)-1):OFFSET($A$1,ROW(A101)+(COLUMN(A1)-1)-94,COLUMN(A1)-1))

Regards

**gautamacharya** · 10-12-2011, 04:42 PM

Originally Posted by NBVC

Try:

=SUM(INDEX(A$1:A$40,1+COLUMNS($A$1:A$1)-COLUMNS($A1)):INDEX(A$1:A$40,7+COLUMNS($A$1:A$1)-COLUMNS($A1)))

adjust the A1:A40 range to suit height of the whole range from A1 to last column in D....

and copy across.

Thank you NBVC- It works perfectly.