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

    SUMIF With RangeNames (XP; SR3)

    I would like to utilize range names in a SUMIF formula. The range name would refer to the column letter the range names was entered in.

    As an example:
    RangeName entered in Cell K1 = MyRangeName

    Without using range names the formula would be: =SUMIF(A:A,B5,K:K)

    I was thinking of something like: =SUMIF(A:A,B5,LEFT(ADDRESS(1,COLUMN(MyRangeName),4 ),LEN(ADDRESS(1,COLUMN(MyRangeName),4))-1)&":"&LEFT(ADDRESS(1,COLUMN(MyRangeName),4),LEN(A DDRESS(1,COLUMN(MyRangeName),4))-1))

    The reason I'm going with the range name approach is that the formula will eventually be on a different sheet and the range will move as new columns are inserted on the other sheet.

    Any thoughts would be appreciated,
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF With RangeNames (XP; SR3)

    I am not at all sure what you are trying to accomplish, but if you name the range K:K MyRangeName, then you should be able to do this:

    <pre>=SUMIF(A:A,B5,MyRangeName)
    </pre>

    Legare Coleman

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

    Re: SUMIF With RangeNames (XP; SR3)

    Legare,

    I never thought to name the entire column as the range name. It worked.

    Thanks for the suggestion,
    John

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF With RangeNames (XP; SR3)

    Since you are on Excel 2003, you could turn the data area into a list by running Data|List|Create List. The formulas that refer the ranges from a such list will automatically update whenever you add new records to the data area. The setup makes named ranges unnecessary.
    Microsoft MVP - Excel

Posting Permissions

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