Sumproduct in Access

D

Duane Hookom

If I understand correctly assuming you have values in a field [Num] with
values fo 2, 3, and 4 you would want a result of 2*3*4 or 24? If so, you can
create a totals query and use a calculation that uses Exp() against the
Sum() of the Log() of the Num


Exp(Sum(Log([Num])))
 
J

Job

Duane,

For example, I have columns [FTE] and [DaysToFinish]
The values may be .7,.6,.4 and 3.5,2.9,2.2 respectively. The sumproduct
would take (.7 * 3.5) +(.6*2.9)+(.4*2.2) = 5.07

Job



Duane Hookom said:
If I understand correctly assuming you have values in a field [Num] with
values fo 2, 3, and 4 you would want a result of 2*3*4 or 24? If so, you
can create a totals query and use a calculation that uses Exp() against
the Sum() of the Log() of the Num


Exp(Sum(Log([Num])))

--
Duane Hookom
MS Access MVP


Job said:
Is it possible to do a SumProduct like function in Access?
Click to expand...
Click to expand...
 
E

Ed Robichaud

As you've discovered there is no SumProduct function in Access. You can
somewhat roll your own, by using a form, with a grid layout of the fields
involved, then creating unbound controls (which need not be visible) to hold
the products, then use more of those unbound controls to hold the
expressions summing those products.

You might also want to look at X-tab queries to do sums, then add an
expression to handle the multiplication.
-Ed
 
Top