sum a range of unknown size

yuryyuryyury

New Member
Joined
Jul 3, 2007
Messages
19
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?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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?
 
Upvote 0
=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.
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top