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 :

Date	Item	Cost	helper
5-Aug	4512	225	225
5-Aug	4514	425	425
5-Aug	4518	125	350
5-Aug	4518	225
5-Aug	4504	375	375

<tbody>
</tbody>

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

Pivot would give:

Average of helper
Row Labels	Total
4504	375
4512	225
4514	425
4518	350
Grand Total	343.75

<tbody>
</tbody>

With in ∑Values 'helper' set on average.

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