ExcelBanter - Help with Formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Help with Formula (https://www.excelbanter.com/excel-worksheet-functions/68358-help-formula.html)

Help with Formula

I have 2 tables, A and B.

TableA

Side Symbol
Buy VKTA07C60.00
Buy SWFN06P65.00
Sell VAJA07C60.00
Buy OJMA07C55.00
Buy QQQO06P42.00
Buy QQQO06P42.00
Buy GE B06C35.00

TableB

Side Symbol TradeVolume
Buy GE B06C35.00 72
Buy QQQO06P42.00 360
Buy QQQO06P42.00 540
Buy QQQO06P42.00 100
Buy OJMA07C55.00 25
Sell OJMA07C55.00 50
Buy OJMA07C55.00 50
Buy OJMA07C55.00 25
Sell VAJA07C60.00 50
Buy SWFN06P65.00 67
Buy SWFN06P65.00 11
Sell SWFN06P65.00 52
Buy SWFN06P65.00 52
Buy SWFN06P65.00 124
Buy SWFN06P65.00 246
Sell VKTA07C60.00 462
Buy VKTA07C60.00 462

I am looking for a formula to place in ColC of Table A that will look at
Table B, Find the match for value in ColB Symbol, and if ColA in Table A is
"Buy", find "Sell" in ColA of Table B (similar if Table A is "Sell", Table B
match needs to be "Buy". If the 2 conditions are met, sum the values in ColC
of Table B.

Hope I explained my problem ok.

Thank you in advance.

Help with Formula

Assumptions:

A1:B8 contains Table A

E1:G18 contains Table B

Formula:

C2, copied down:

=SUMPRODUCT(--(E$2:E$18=IF(A2="Buy","Sell",IF(A2="Sell","Buy"))) ,--(F$2:F
$18=B2),G$2:G$18)

Hope this helps!

In article ,
"carl" wrote:

I have 2 tables, A and B.

TableA

Side Symbol
Buy VKTA07C60.00
Buy SWFN06P65.00
Sell VAJA07C60.00
Buy OJMA07C55.00
Buy QQQO06P42.00
Buy QQQO06P42.00
Buy GE B06C35.00

TableB

Side Symbol TradeVolume
Buy GE B06C35.00 72
Buy QQQO06P42.00 360
Buy QQQO06P42.00 540
Buy QQQO06P42.00 100
Buy OJMA07C55.00 25
Sell OJMA07C55.00 50
Buy OJMA07C55.00 50
Buy OJMA07C55.00 25
Sell VAJA07C60.00 50
Buy SWFN06P65.00 67
Buy SWFN06P65.00 11
Sell SWFN06P65.00 52
Buy SWFN06P65.00 52
Buy SWFN06P65.00 124
Buy SWFN06P65.00 246
Sell VKTA07C60.00 462
Buy VKTA07C60.00 462

I am looking for a formula to place in ColC of Table A that will look at
Table B, Find the match for value in ColB Symbol, and if ColA in Table A is
"Buy", find "Sell" in ColA of Table B (similar if Table A is "Sell", Table B
match needs to be "Buy". If the 2 conditions are met, sum the values in ColC
of Table B.

Hope I explained my problem ok.

Thank you in advance.