Pivot IF Formula

[david.keogh15]

Hi,

I am trying to add a formula to a Pivot table below but it does not seem to be picking up that if the Product Category data is equal to Primary. Is it doing a sum on Product Category and not identifying the value beneath? Product Category has two Sets of results either Primary or Secondary. If it is Primary I want to Sum Field Contribution V's Eurex but if it is not Primary to Sum Contribution over Average. The formula result is just giving the False result

= IF('Product Category'="Primary",SUM('Contribution V''s Eurex'),SUM('Contribution Over Average'))

Any help would be greatly appreciated

Thanks
David

[romperstomper]

In a calculated field formula, any field reference is summed before anything else happens. So your formula is actually in effect this:

= IF(SUM('Product Category')="Primary",SUM('Contribution V''s Eurex'),SUM('Contribution Over Average'))

Since the category field is text, the result of the sum will always be 0, so you'll always get the formula from the False part of the If.

I'd suggest you amend the source data to add a column that returns the value of the relevant contribution field, then just SUM that.

[david.keogh15]

Thanks, That s pity now as I don't have the data, I was only sent a copy of the pivot.

[romperstomper]

If you amend the pivot format so that there is a grand total cell, then double-click it, you may get a copy of the data.