Results 1 to 6 of 6

sum a range of unknown size

This is a discussion on sum a range of unknown size within the Excel Questions forums, part of the Question Forums category; In cell C6, I have this formula: Code: =C5*(SUMPRODUCT(ABS($B13:$B2011))) Since I don't know how long column B would be, I ...

  1. #1
    New Member
    Join Date
    Jul 2007
    Posts
    19

    Default sum a range of unknown size

    In cell C6, I have this formula:

    Code:
    =C5*(SUMPRODUCT(ABS($B13:$B2011)))
    Since I don't know how long column B would be, I picked a large number. Is there a way to incorporate a Range("A65536").End(xlUp) in there somehow?

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    =C5*(SUMPRODUCT(ABS($B13:INDEX($B:$B,MATCH(9.9999E+307,$B:$B)))))

  3. #3
    Board Regular TTom's Avatar
    Join Date
    Jan 2005
    Location
    Oregon
    Posts
    514

    Default

    Could you use VBA to name the variable range:
    ActiveWorkbook.Names.Add Name:="rng_PSum", RefersTo:="=Sheet1!$B$13:$B$" & Range("A6000").End(xlUp).Row

    Then substitute for ($B13:$B2011) the named range: (rng_PSum)
    thus,
    =C5*(SUMPRODUCT(ABS(rng_PSum)))
    or, write the rest of ProductSum as formula in VBA for Range("C6").Value?
    Yesterday gives us the tools today to create our tommorrow... tt

  4. #4
    New Member
    Join Date
    Jul 2007
    Posts
    19

    Default

    =C5*(SUMPRODUCT(ABS($B13:INDEX($B:$B,MATCH(9.9999E+307,$B:$B)))))
    this worked perfectly! thank you!

    Btw, can someone explain what is going on in the formula? I'm not too familiar with the index and match functions.

  5. #5
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    B13:INDEX($B:$B,MATCH(9.9999E+307,$B:$B))

    index() can return a reference as well as a value.

    you're indexing b:b
    the 9.9999E+307 is something of a 'trick' - basically, if you look for a number larger than any number in a range of numbers (& 9.9999E+307 is about the largest number excel can cope with, so works in most cases), match() will return the position of the last number on the list, which is what we want...

  6. #6
    New Member
    Join Date
    Jul 2007
    Posts
    19

    Default

    thanks!

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
  •  


DMCA.com