You can add a column to the source data, if it's an Excel database, and
do the row calculations there. Then, add the new field to the pivot
table, and you'll get the correct total.

tedy wrote:
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-



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html