Dividing after sumproduct

[rsd007]

Hi,
Trying to do a project with keeping accurate real cost after sale of item , I was able to do when only item is purchased but stuck as soon as item get sold.
Formula trying to modify and use is
=IF(OR(D2=$A$3:$A$5),SUMPRODUCT($F$2:F2*ABS($H$2:H2)*($E$2:E2=E2))*(ISNUMBER(MATCH($D2:D$3,$A$3:$A$6,0))),"")-(LOOKUP(2,IFERROR(1/($E$3:E3=E3),(ISNUMBER($I$3:I3/$G$3:G3)))*($E$3:E3=E3)*(ISNUMBER(MATCH($D$3:D3,$A$6:$A$7,0)))))

first half of the formula is calculating but then adding 2nd half does not work. When using 1st half formula can see it working till row 18
Problem start from Row18 when sale take place have highlight with the answer looking for. Also trying @ mrExcel will add ref once i post

Thanks

 

[bosco_yip]

Try,

In J2, formula copied down:

=IF(OR(D2=$A$3:$A$5),SUMPRODUCT($F$2:F2*ABS($H$2:H2)*($E$2:E2=E2))*(ISNUMBER(MATCH($D2:D2,$A$3:$A$5,0))),IF(D2="Sell",F2*(LOOKUP(1,0/(D$1:D1="Buy"),I$1:I1)/G2),IF(D2="Sold",LOOKUP(1,0/(D$1:D1="Bought"),I$1:I1)-F2*(LOOKUP(1,0/(D$1:D1="Bought"),I$1:I1)/G2),"")))

 

[rsd007]

Thankyou bosco_yip

For some reason I am not getting same answer in row 20.

 

[rsd007]

This is the best support Group with fast support.

Also is it possible instead of adding SOLD, SELL, BUY, BOUGHT separately, can be point to the group cell A3 to A7 so the formula will be shorter. Reason is (Sell, Sold) should be treated same and (Buy, Bought or In transit) should be treated as same.

=IF(OR(D2=$A$3:$A$5),SUMPRODUCT($F$2:F2*ABS($H$2:H2)*($E$2:E2=E2))*(ISNUMBER(MATCH($D2:D2,$A$3:$A$5,0))),IF(D2="Sell",F2*(LOOKUP(1,0/(D$1:D1="Buy"),I$1:I1)/G2),IF(D2="Sold",LOOKUP(1,0/(D$1:D1="Bought"),I$1:I1)-F2*(LOOKUP(1,0/(D$1:D1="Bought"),I$1:I1)/G2),"")))

More like this
=IF(OR(D18=$A$3:$A$5),SUMPRODUCT($F$2:F18*ABS($H$2:H18)*($E$2:E18=E18))*(ISNUMBER(MATCH($D18:D18,$A$3:$A$5,0))),IF(D18=$A$6:$A$7,F18*(LOOKUP(1,0/(D$1:D17=$A$3:$A$5),I$1:I17)/G$1:G17),IF(D18=$A$6:$A$7,LOOKUP(1,0/(D$1:D17=$A$3:$A$5),I$1:I17)-F18*(LOOKUP(1,0/(D$1:D17=$A$3:$A$5),I$1:I17)/G$1:G17),"")))

Problem is when I use this I get #Value