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. 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. =C5*(SUMPRODUCT(ABS(\$B13:INDEX(\$B:\$B,MATCH(9.9999E+307,\$B:\$B)))))

3. Could you use VBA to name the variable range:

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?

4. =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. 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. thanks!

Posting Permissions

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