# Dragging formulas across columns while incrementing rows

1. ## Dragging formulas across columns while incrementing rows

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

2. ## Re: Dragging formulas across columns while incrementing rows

``Please Login or Register  to view this content.``
paste and drag accross...worked for me

3. ## Re: Dragging formulas across columns while incrementing rows

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.

4. ## Re: Dragging formulas across columns while incrementing rows

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

5. ## Re: Dragging formulas across columns while incrementing rows

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.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1