| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2 Nesting questions | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Hide formula | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Linear Mode
