+ Reply to Thread
Results 1 to 7 of 7

SUM using ADDRESS function and Named Ranges

  1. #1
    Registered User
    Join Date
    10-02-2012
    Location
    colorado
    MS-Off Ver
    excel 2007
    Posts
    11

    SUM using ADDRESS function and Named Ranges

    I am trying to SUM a range of cells in another sheet within the same worksheet/workbook.

    The formula I want is =SUM(Ph!BP15:Ph!BW15)

    However, to future proof this formula, since the BP:BW range may change at some point, I created a Named Range called "Gutters" which is BP:BW in the Sheet "Ph".

    The row I need to reference is contained in cell B1 of the current sheet I am writing the formula in. So the formula I have now is:

    =sum(address(B1,COLUMN(gutters),4,true,"Ph")&":"&address(B1,column(gutters)+COLUMNS(gutters)-1,4,true,"Ph"))

    Where B1 contains a row number, and "Ph" is the name of another sheet in the worksheet.

    I am using COLUMN(gutters) to tell me the number of the first column of the named range Gutters, because I am trying to sum all of the cells in row 15 within the named range Gutters.

    Using:

    address(B1,COLUMN(gutters),4,true,"Ph")&":"&address(B1,column(gutters)+COLUMNS(gutters)-1,4,true,"Ph")

    returns exactly the text I want, "Ph!BP15:Ph!BW15", so I figured I could put it within a SUM function and it would sum that range. But it actually returns the number 0. There are numbers in all those cells, so I am puzzled why it's returning 0. It does not give me an error. If I simply put =SUM(Ph!BP15:Ph!BW15) it returns the correct sum of 179. So why isn't it returning 179 when I derive it using the address functions separated by &":"& ?

    I also tried adding INDIRECT( to each address function and this still returned 0.

    Any help?

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: SUM using ADDRESS function and Named Ranges

    Quote Originally Posted by excelroofing View Post
    However, to future proof this formula, since the BP:BW range may change at some point, I created a Named Range called "Gutters" which is BP:BW in the Sheet "Ph".
    That's not clear to me. I would make Gutters a global name with the reference Ph!BP:BW. Is that what you mean?

    Quote Originally Posted by excelroofing View Post
    =sum(address(B1,COLUMN(gutters),4,true,"Ph")&":"&address(B1,column(gutters)+COLUMNS(gutters)-1,4,true,"Ph"))
    I would avoid INDIRECT(ADDRESS...) expressions. INDIRECT is a volatile function, which gets re-evaluated whenever Excel performs any recalculation (e.g., each edit).

    Try the following (untested):

    =SUM(INDEX(Gutters,B1,COLUMN(Gutters)):INDEX(Gutters,B1,COLUMN(Gutters)+COLUMNS(Gutters)-1))

  3. #3
    Registered User
    Join Date
    10-02-2012
    Location
    colorado
    MS-Off Ver
    excel 2007
    Posts
    11

    Re: SUM using ADDRESS function and Named Ranges

    Error
    Argument must be a range.

  4. #4
    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 using ADDRESS function and Named Ranges

    Hard to say without seeing what you are working with. Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    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

  5. #5
    Registered User
    Join Date
    10-02-2012
    Location
    colorado
    MS-Off Ver
    excel 2007
    Posts
    11

    Re: SUM using ADDRESS function and Named Ranges

    Oops I forgot to say I need it to work in Google sheets. I've made a simple sheet to recreate the problem. Feel free to write some attempts in the formula areas:

    https://docs.google.com/spreadsheets...it?usp=sharing

  6. #6
    Registered User
    Join Date
    10-02-2012
    Location
    colorado
    MS-Off Ver
    excel 2007
    Posts
    11

    Re: SUM using ADDRESS function and Named Ranges

    =SUM(INDEX(Gutters,B1)) is the solution

  7. #7
    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 using ADDRESS function and Named Ranges

    Thanks for sharing, its always a good feeling when you answer your own questions

+ 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. [SOLVED] Use ISBLANK Function with Named Range Instead of Cell Address
    By Toner in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-11-2012, 02:33 PM
  2. Named ranges as values of IF function
    By goels in forum Excel General
    Replies: 4
    Last Post: 10-21-2011, 11:51 AM
  3. Excel 2007 : Named Ranges and Max Function
    By AberdeenGDK in forum Excel General
    Replies: 3
    Last Post: 08-19-2010, 01:27 PM
  4. Problem with OFFSET function used in Named Ranges
    By acsishere in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-12-2008, 02:24 PM
  5. Excel 2007 : Named ranges no longer function
    By Rick_Stanich in forum Excel General
    Replies: 1
    Last Post: 08-21-2008, 11:11 AM
  6. Replies: 1
    Last Post: 09-28-2005, 04:05 PM
  7. [SOLVED] Replace a spreadsheets named cells/ranges with exact cell address.
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-16-2005, 02:05 PM
  8. Replace a spreadsheets named cells/ranges with exact cell address!
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2005, 08:05 AM

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