Sumproduct inside a pivot table help!!

[dayday123]

Hi -

I am in desperate need of some pivot table help!

I have a pivot table as shown below. The column titled "Value 1" is a calculated field that I added to my pivot table. The Column Called "Tier Weight" is the average of static values that are in my raw data sheet based on whether or not the value is in tier 1, tier 2, or tier 3.

What I want to do is be able to get a product total based on the weighted value of each of the tiers. So for example currently Product 1 is showing me a value of 76.0%. However if I were to weight the 89.3% at 16%, the 70.0% at 19% and the 56.1% at 65% then the total value of Product 1 should actually be 64.1%

How can I get the pivot table to calculate this for me??!!

Also the 34%, 53%, and 36% are meaningless to me. The pivot table is just calculating them based on the Tiers but only the Tiers should have weights. Not the products themselves.

I really appreciate any help!!!! Thanks!

[TABLE="width: 310"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Value 1[/TD]
[TD]Tier Weight [/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]76.0%[/TD]
[TD]34%[/TD]
[/TR]
[TR]
[TD]Tier 1[/TD]
[TD]89.3%[/TD]
[TD]16%[/TD]
[/TR]
[TR]
[TD]Tier 2[/TD]
[TD]70.0%[/TD]
[TD]19%[/TD]
[/TR]
[TR]
[TD]Tier 3[/TD]
[TD]56.1%[/TD]
[TD]65%[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]72.5%[/TD]
[TD]53%[/TD]
[/TR]
[TR]
[TD]Tier 1[/TD]
[TD]90.1%[/TD]
[TD]16%[/TD]
[/TR]
[TR]
[TD]Tier 2[/TD]
[TD]77.7%[/TD]
[TD]19%[/TD]
[/TR]
[TR]
[TD]Tier 3[/TD]
[TD]70.2%[/TD]
[TD]65%[/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD]78.4%[/TD]
[TD]36%[/TD]
[/TR]
[TR]
[TD]Tier 1[/TD]
[TD]89.8%[/TD]
[TD]16%[/TD]
[/TR]
[TR]
[TD]Tier 2[/TD]
[TD]72.8%[/TD]
[TD]19%[/TD]
[/TR]
[TR]
[TD]Tier 3[/TD]
[TD]76.6%[/TD]
[TD]65%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]78.0%[/TD]
[TD]38%
[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hi,

Have a look at this thread : http://www.mrexcel.com/forum/excel-questions/512603-sumproduct-formula-inside-pivot-table.html

HTH