If you want speed, calculate the sums in another column, say M, =E5*L5 etc.,
and use

=SUBTOTAL(9,M:M6)/E3

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JDaywalt" wrote in message
...
I have seen several posts that provide recommendations for calculating
Sumproduct when using an autofilter. All of the recommendations require
use
of the Offset function to achieve the result. While I have tried several
of
the suggested calculations and they do work correctly, the issue I have is
that using Offset makes the recalculation process much lengthier---and
because Offset is a "volatile" function it triggers a recalculation
anytime I
make a change anywhere in the spreadsheet. Two questions:

Here is an example of what I'm trying to do:

Range E5:E1000 contains unit volume in whole numbers (i.e. 1,200)
Range L5:L1000 contains dollar values (i.e. $12.45)

I need to calculate the Sumproduct for these two columns, then divide this
result by the total unit volume to achieve a weighted average "rate".
Here
is the formula I am using in row 4 -- again, it does work correctly:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E5:E65536)-ROW(E4),1)),E5:E65536,L5:L65536)/E3

Is there any other way to calculate this"weighted average rate" without
using the Offset function---or any other 'volatile' function? Perhaps
using
VB in some creative way? I realize a simple solution would be to could
carry
out the Volume * Rate calculation for each row & then perform the subtotal
calculation at the top, but this would require me to insert 64 calculated
columns into a worksheet that already contains 205 columns---not very
efficient

Any assistance or ideas would be greatly appreciated.