Weighted Average and Conditional Totals

[kmpmba]

I need the formula for the highlighted columns, I tried using Pivot Table but it does not help as average is not based on quantity purchased. Also cannot find a way to total BUY and SELL separately. I do not mind changing the data structure / adding columns if needed (transactions) Please assist; - cross posted

 

[offthelip]

Put the following in cell I2:
=COUNTIFS(A$2:A$20,H2,B$2:B$20,"BUY")
and the following in Cells J2:
=SUMIFS(F$2:F$20,A$2:A$20,H2,B$2:B$20,"BUY")/I2
The equations in L ad M and the same except BUY is changed to SELL
Copy down