Thanks:  0
Likes:  0

# Thread: sum a range of unknown size

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!

## User Tag List

#### Posting Permissions

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