Invoicing Problem

[carl]

My Data Table looks like this:

Qty Buyer Seller ChargedSide
2 BOX980 BOX226 Buyer
30 BOX226 BOX917 Seller


I am trying to create a new table like this:

Charge Credit
BOX980 Formula (2 x $50) Formula (0 x $25)
BOX917 Formula (30 x 50) Formula (0 x $25)
BOX226 Formula (0 x 50) Formula (2x25 + 30x25)

So for each customer in ColA, look at the data table, determine whether the
charder side is the buyer or seller, and then apply the formula to determine
the Charge or Credit.

Thank you in advance.

 

[Toppers]

Using the ranges below:

A B C D
Qty Buyer Seller ChargedSide <== Row 1
2 BOX980 BOX226 Buyer
30 BOX226 BOX917 Seller
5 BOX980 BOX226 Buyer
10 BOX226 BOX917 Seller

A B C
Charge Credit <== Row 8
BOX980 350 0
BOX917 2000 0
BOX226 0 1175

in B9:

=(SUMPRODUCT(--($B$2:$B$5=$A9),--($D$2:$D$5=$B$1),($A$2:$A$5))+SUMPRODUCT(--($C$2:$C$5=$A9),--($D$2:$D$5=$C$1),($A$2:$A$5)))*50

in C9:

=(SUMPRODUCT(--($C$2:$C$5=$A9),--($D$2:$D$5<>$C$1)*($A$2:$A$5))+SUMPRODUCT(--($B$2:$B$5=$A9),--($D$2:$D$5<>$B$1),($A$2:$A$5)))*25

I have allowed for multiple entries of a "Buyer"/"Seller"

HTH