+ Reply to Thread
Results 1 to 5 of 5

Sum function but with variable cell reference

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    Brussels
    MS-Off Ver
    Excel 2003
    Posts
    3

    Sum function but with variable cell reference

    Hello,

    Sorry if there is already a post for my question but I didn't find any.

    I already search for several days how to sum cell content but using relative position. In the worksheet I have several contracts, each contracts have several amounts, not always the same number of amount : I would like to sum those amounts per contract.
    I have already in different cells :
    - the number of amount
    - the begin location of the amount for the contract : "=ADDRESS(N33;10)"
    - the last location of amount for the contract : "=ADDRESS(O33;10)"
    but I can't do : "=sum(ADDRESS(N33;10):ADDRESS(O33;10)" or something like that without having an error.
    The worksheet contains around 30000 lines.
    I don't want to do it in VBA (because I'm not used to :-( )

    Thank you for your help (and your patience).
    Renée

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sum function but with variable cell reference

    Hi and welcome to the forum

    sounds like you need to use the INDIRECT() function?

    If you still have a problem, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Sum function but with variable cell reference

    welcome to the forum, Renee. you could do it like this:
    =SUM(INDIRECT(ADDRESS(N33,10)&":"&ADDRESS(O33,10)))

    but INDIRECT is volatile & will slow down your workbook. so this might be better:
    =SUM(INDEX(J:J,N33):INDEX(J:J,O33))

    change my commas to semi-colons for it to work on your regional settings

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    10-09-2013
    Location
    Brussels
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Sum function but with variable cell reference

    Thank you benishiryo ! Great ! It works exactly the way I need ... and I tried so many ways but not INDIRECT or INDEX;

  5. #5
    Registered User
    Join Date
    10-09-2013
    Location
    Brussels
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Sum function but with variable cell reference

    FDibbins, I forgot to put the file. Next time ;-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. OFFSET function with variable reference cell
    By smile2leksa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2009, 08:32 AM
  2. [SOLVED] MIN Function w/ variable address reference
    By WLMPilot in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 07:05 AM
  3. [SOLVED] MIN Function w/ variable address reference
    By WLMPilot in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-06-2005, 06:05 AM
  4. [SOLVED] MIN Function w/ variable address reference
    By WLMPilot in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-06-2005, 04:05 AM
  5. MIN Function w/ variable address reference
    By WLMPilot in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-10-2005, 03:05 PM

Tags for this Thread

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