Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    That Sums It Up (XL97:SR2)

    Is it possible to have a formula that sums a range of cells in a column (based on a column of a ranged cell).

    Example:
    Cell G1 contains the range name "rngOne"

    The formula should end up being something like: SUM(Cells(4,column(rngOne), Cells(10, column(rngOne))

    The obvious would be to make the formula = SUM(G4:G10) but I'm trying to pass the column of the range named "rngOne" into the formula.

    John <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: That Sums It Up (XL97:SR2)

    I think you are looking for the INDIRECT function.
    If rngOne is defined as G4:G10, and in G1 you have typed the text "rngOne"
    The following:
    =SUM(INDIRECT((ADDRESS(4,COLUMN(rngone)))&":"&ADDR ESS(10,COLUMN(rngone))))
    will sum the cells in G4:G10.

    as well as ,
    =SUM(INDIRECT(ADDRESS(4,COLUMN(INDIRECT(G1)))&":"& ADDRESS(10,COLUMN(INDIRECT(G1)))))

    and the simpler:
    =SUM(INDIRECT(G1))
    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Sums It Up (XL97:SR2)

    Steve,

    Your suggestion works as long as the formula and range is on the same sheet. What happens if the formula is on a different sheet (say Sheet2) and referencing the range name and data from Sheet1.

    One would think that "ADDRESS(4,COLUMM(rngone))" should be replaced by "ADDRESS(4,COLUMM(rngone),2,FALSE,"Sheet1"))"

    Unfortunately I receive nothing but #REF as the returned value. Am I missing something?

    Thanks,
    John

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: That Sums It Up (XL97:SR2)

    Did you try something like this:

    =SUM(INDIRECT(ADDRESS(4,COLUMN(rngOne),1,TRUE,"She et2")&":"&ADDRESS(10,COLUMN(rngOne))))

    1) you need want the first address to include the sheet
    2) you want "TRUE" not "FALSE" if you always want to refer to G4 no matter what cell the formula is in. If you use false and are in A1 it will refer to G4, but if you are in B2 it will refer to H5 (4 rows down, 7 columns across, relative to the current cell). If you want to refer to a cell RELATIVE to the cell with the calc then you want FALSE.

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Sums It Up (XL97:SR2)

    Use this:

    =SUM(INDIRECT("Sheet1!" & G1 & "4:" & G1 & "10"))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: That Sums It Up (XL97:SR2)

    Jan,
    This will only work if G1 contains the column letter of the desired column, not a range name as he originally proposed.

    Steve

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Sums It Up (XL97:SR2)

    <img src=/S/woops.gif border=0 alt=woops width=58 height=36> Of course. Proves I wasn't paying attention while reading the first post
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    New Lounger
    Join Date
    May 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Sums It Up (XL97:SR2)

    Try this.

    Give a RangeName to the cell where you will enter the range to sum.
    Then use this:=SUM(INDIRECT(CLEAN(text))). Where text = cell containing range to sum.

    Regards,

    Nick

Posting Permissions

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