Pivot Table Help

[zonemaster88]

Hi, I am trying to get the average cost of items with duplicates of the same item but having different value in a pivot table. I tried looking for an answer on here and was unable to find one.

Ex:
Date Item Cost
8/5 4512 $225
8/5 4514 $425
8/5 4518 $125
8/5 4518 $225
8/5 4504 $375

A pivot table would get the average of $275 when I sort by date. However, the average I am looking for is of all the orders with 4518 combined. This would be $343.75. I understand that I could just sort this by item # but the actual data I'm working with has over 80,000 items (in-practical). Anyone know how to combine item numbers and get the average of combined like items in a pivot table?

Thanks!

 

[cyrilbrd]

with helper column as follows :
[TABLE="width: 260"]
<tbody>[TR]
[TD="class: xl64, width: 65"]Date[/TD]
[TD="class: xl64, width: 65"] Item[/TD]
[TD="class: xl64, width: 65"] Cost[/TD]
[TD="class: xl64, width: 65"]helper[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5-Aug[/TD]
[TD="class: xl64, align: right"]4512[/TD]
[TD="class: xl64, align: right"]225[/TD]
[TD="align: right"]225[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5-Aug[/TD]
[TD="class: xl64, align: right"]4514[/TD]
[TD="class: xl64, align: right"]425[/TD]
[TD="align: right"]425[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5-Aug[/TD]
[TD="class: xl64, align: right"]4518[/TD]
[TD="class: xl64, align: right"]125[/TD]
[TD="align: right"]350[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5-Aug[/TD]
[TD="class: xl64, align: right"]4518[/TD]
[TD="class: xl64, align: right"]225[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5-Aug[/TD]
[TD="class: xl64, align: right"]4504[/TD]
[TD="class: xl64, align: right"]375[/TD]
[TD="align: right"]375[/TD]
[/TR]
</tbody>[/TABLE]

formula in D2 is =IF(COUNTIF($B$2:B2,B2)>1,"",SUMIF($B$2:$B$6,B2,$C$2:$C$6))

Pivot would give:
[TABLE="width: 139"]
<tbody>[TR]
[TD]Average of helper[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]4504[/TD]
[TD="align: right"]375[/TD]
[/TR]
[TR]
[TD]4512[/TD]
[TD="align: right"]225[/TD]
[/TR]
[TR]
[TD]4514[/TD]
[TD="align: right"]425[/TD]
[/TR]
[TR]
[TD]4518[/TD]
[TD="align: right"]350[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]343.75[/TD]
[/TR]
</tbody>[/TABLE]

With in ∑Values 'helper' set on average.

Would that work?
Unless mistaken Calculated fields do not do to well with sum and count.