Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Variable Range for SUM Function

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried posting this once but it didn't seem to work, my apologies if it appears twice.

    I would like to dynamically construct a range to be used in a SUM function but I can not get the addresses into the function after constructing them.

    For example, I create the addresses of lower and upper limits of the range using the ADDRESS function, and then I want to put them in the SUM function. I thought the following would work to add up only the data under the months up to this month (I have the data for 12 months in row 5 starting at column H):

    SUM(ADDRESS(ROW(F5),8,4):ADDRESS(ROW(F5),(7+MONTH(TODAY())),4))

    but the output from the ADDRESS function is text and not accepted as an actual address.

    Can anyone help?

    Thank you,
    Brian

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you could use indirect

    =SUM(INDIRECT(ADDRESS(ROW(F5),8,4)&":"&ADDRESS(ROW(F5),(7+MONTH(TODAY())),4)))

    Or depending on your criteria, maybe a sumif statement maybe better suited. I'm not going to pretend I tried to figure out what you wanted to add here.

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-02 11:52, bajturner wrote:
    I tried posting this once but it didn't seem to work, my apologies if it appears twice.

    I would like to dynamically construct a range to be used in a SUM function but I can not get the addresses into the function after constructing them.

    For example, I create the addresses of lower and upper limits of the range using the ADDRESS function, and then I want to put them in the SUM function. I thought the following would work to add up only the data under the months up to this month (I have the data for 12 months in row 5 starting at column H):

    SUM(ADDRESS(ROW(F5),8,4):ADDRESS(ROW(F5),(7+MONTH(TODAY())),4))

    but the output from the ADDRESS function is text and not accepted as an actual address.

    Can anyone help?

    Thank you,
    Brian
    Brian,

    If you build a range reference using the ADDRESS worksheet function, you need to deference it with INDIRECT before feeding the built range to another worksheet function.

    So:

    =SUM(INDIRECT(ADDRESS(ROW(F5),8,4)&":"&ADDRESS(ROW(F5),(7+MONTH(TODAY())),4)))

    should work.

    I might as well add that although fully legal, it's not most efficient way.

    Regards,

    Aladin

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The INDIRECT function returns the reference specified by text. Try something like:

    SUM(IDIRECT(ADDRESS(ROW(F5),8,4)&":"&ADDRESS(ROW(F5),(7+MONTH(TODAY())),4)))


  5. #5
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you both for you replies. It worked! I had tried using INDIRECT but did not include the &":"&. That was the problem.

    Just out of curiosity Aladin, what is a more elegant way to achieve the same thing?

    Brian

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-02 12:07, bajturner wrote:
    Thank you both for you replies. It worked! I had tried using INDIRECT but did not include the &":"&. That was the problem.

    Just out of curiosity Aladin, what is a more elegant way to achieve the same thing?

    Brian

    I'd use the OFFSET worksheet function to specify the range over which to compute SUM, etc.

    But, try to figure out whether the SUMIF function would serve your purpose as IML noted.

    Aladin

  7. #7
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    SUMIF worked and was much simpler.

    Thanks again,
    Brian

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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