hi all,

im trying to create a datafield for pivot table in vba, which is
sumproduct from two other fields..... so far, i have coded as shown
below:

Code:


strDataField = "No_Of_Units * Unit_Value"
pivot.CalculatedFields.Add "TotalUnitValue", strDataField, True
Set pv = pivot.PivotFields("TotalUnitValue")
pv.Orientation = xlDataField
pv.Position = 1

however, it calculates the product of sum of each fields, instead of
the sum of the product of each item in the field (the underlying
data)....

say i have:
unit value, no_of_units
1,2
2,3
3,4

what i wanted is sum of (1*2) + (2*3) + (3*4).
but what that piece of code gave me was (1+2+3) *(2*3*4)

anyone help is greatly appreciated!

-ted-