+ Reply to Thread
Results 1 to 6 of 6

Summing

  1. #1
    Registered User
    Join Date
    06-13-2013
    Location
    Iowa
    MS-Off Ver
    Excel 2010
    Posts
    10

    Question Summing

    I am trying to sum two cells, however, the locations of these cells are determined by there own formulas which renders the sum formula useless. I am unable to find a substitute formula that will allow me to use a formula as a starting and end point. Does anybody know of a good substitute formula for this?
    Last edited by seth2613; 06-13-2013 at 02:49 PM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2504
    Posts
    13,631

    Re: Summing

    Quote Originally Posted by seth2613 View Post
    however, the locations of these cells are determined by there own formulas which renders the sum formula useless. I am unable to find a substitute formula that will allow me to use a formula as a starting and end point.
    What do you mean? Could you please post a sample sheet ( not a pic)?

  3. #3
    Registered User
    Join Date
    06-13-2013
    Location
    Iowa
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Summing

    Take for instance the basic sum formula
    =sum(C17:C19)
    C17 would be the starting point and C19 would be the end point. However I am using a large formula to determine these points. The formula to determine the starting point is
    =ADDRESS(LOOKUP(2,1/(B11:B2011="Received"),ROW(B11:B2011)),3,4) which gives C17

    Quite complex I know, but it gives me an accurate location fo what I need. I just cant apply it into a formula.
    Last edited by seth2613; 06-13-2013 at 02:58 PM.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Summing

    If your formula was located in E1 for example then to use the value returned you could have a formula like this:

    =SUM(INDIRECT(E1):C17) This takes the value returned by your formula in E1 and creates a SUM range that ends with C17. If you use your formula with some other criteria for the end point of the sum range, use the INDIRECT function to make use of the address calculated. The formula would then be:

    =SUM(INDIRECT(E1):INDIRECT(SOME OTHER CELL))
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    06-13-2013
    Location
    Iowa
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Summing

    Quote Originally Posted by newdoverman View Post

    =SUM(INDIRECT(E1):INDIRECT(SOME OTHER CELL))
    This is exactly what I needed, thank you.

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Summing

    You're welcome.

    Good luck.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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