+ Reply to Thread
Results 1 to 6 of 6

Sum all numbers in a row defined by changing column numbers

  1. #1
    Registered User
    Join Date
    03-21-2014
    Location
    Leeds, England
    MS-Off Ver
    Excel 2007
    Posts
    36

    Sum all numbers in a row defined by changing column numbers

    Hi,
    I am looking for a simple formula that adds us the number of changing columns defined in Cell A1
    For example, A1 may be "5" so I need a formula that adds all numbers between columns A2:E2 (5 columns) and
    if A1 changes to "6", then I need it to add all numbers between A2:F2 (6 columns) and likewise
    if A1 changes to "4", then I need it to add all numbers between A2:D2 (4 columns) and so on...
    Can't find anything useful in Google so I though I ask the experts.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    14,422

    Re: Sum all numbers in a row defined by changing column numbers

    You can use this formula (maybe in B1):

    =SUM(A2:INDEX(2:2,$A$1))

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-21-2014
    Location
    Leeds, England
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: Sum all numbers in a row defined by changing column numbers

    Thanks Pete. I tried this and it works as I want it but...
    in reality, my sum should start from O9 up to BN9, only adding the number of columns defined in $K$1.
    So I've placed the new formula in G9 (where required) and changed the formula to:
    =SUM(O9:INDEX(9:9,$K$1))
    but it returns me a big fat zero - where am I going wrong?

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    14,422

    Re: Sum all numbers in a row defined by changing column numbers

    It's always a good idea to describe your actual problem and data layout, rather than simplify it - I was able to use 2:2 (or 9:9 in your case) as the data range to be summed started in column A. You will need to change the formula to this:

    =SUM(O9:INDEX(O9:BN9,$K$1))

    to relate to your real layout, where K1 contains the number of columns to be SUMmed (i.e. if K1 is 1, this means to SUM(O9:O9) )

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    03-21-2014
    Location
    Leeds, England
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: Sum all numbers in a row defined by changing column numbers

    Thanks Pete - that worked a treat. Simplifying in this case was not my best option, agreed.

  6. #6
    Registered User
    Join Date
    06-05-2017
    Location
    indonesia
    MS-Off Ver
    marketing
    Posts
    1

    Re: Sum all numbers in a row defined by changing column numbers

    Thankyou friend to make me already join here

+ 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