Help with Formula

C

carl

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.
 
D

Domenic

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumif function with two criterias 4
help with calculating average prices and profit/loss 3
FORMULA FOR TABLE 4
IF with AND and OR 3
Invoice Formula 0
Profit and Loss Formula 1
Lookup Question 2
Formula Help 0

Top