+ Reply to Thread
Results 1 to 5 of 5

Dragging formulas across columns while incrementing rows

  1. #1
    Registered User
    Join Date
    04-20-2011
    Location
    CA
    MS-Off Ver
    Excel 2003
    Posts
    44

    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
    Last edited by gautamacharya; 10-12-2011 at 04:45 PM.

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Dragging formulas across columns while incrementing rows

    Please Login or Register  to view this content.
    paste and drag accross...worked for me
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,042

    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
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    04-20-2011
    Location
    CA
    MS-Off Ver
    Excel 2003
    Posts
    44

    Re: Dragging formulas across columns while incrementing rows

    Quote Originally Posted by NBVC View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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