Hi all,
I have a issue when i have to sum vlookup values. on sheet 1 i have my vlookup value (an ISIN number), on sheets 2 a series of data.
More precisely the data on the sheet 2 are divided as follow: Colunm1,2,3,4 and 5 respectively are BUY/SELL, ISIN, NAME OF THE INSTRUMENT, QUANTITY and HISTORICAL VALUE calculated as (price per share * quantity). This list of data represent all the movement that have been made on several portfolios during a period of time, from these i need to return on Sheet1 the sum of quantity of each instrument that have been trade over a period of time. For example: Lets say that 7 over 10 portfolios have trade in Apples Inc Shares, and there have been 7 purchased during the period of time( each bought 1000 shares therefore 7000 overall). However during the period of time some of the 7 investors have sold their shares, Lets says, 2 of them sold their entire portfolio, so 1000 shares each. As a result a final value of 5000 is remained.
What i want to return in sheet 1 is:
Column 1( Cell A1 i will write my ISIN numbr which is also my lookup value)
Column 2: Sum of each instrument bought during the period of time, (Cell B1 7000 Shares)
Column 3:Sum of each instrument sold during the period of time, (Cell C1 2000 Shares)
Column 4 i Need to do the difference between colum 2 and 3, ( Cell D1 5000)
Column 5, 6 and 7 i need to to the same thing as in colum 2,3 and 4, however instead of using the quantity i need to use the HISTORICAL VALUE, and return and aggregate figures like with the quantities.
Can someone please helping me? How should write the formula?
Best regards,
Lorenzo
I have a issue when i have to sum vlookup values. on sheet 1 i have my vlookup value (an ISIN number), on sheets 2 a series of data.
More precisely the data on the sheet 2 are divided as follow: Colunm1,2,3,4 and 5 respectively are BUY/SELL, ISIN, NAME OF THE INSTRUMENT, QUANTITY and HISTORICAL VALUE calculated as (price per share * quantity). This list of data represent all the movement that have been made on several portfolios during a period of time, from these i need to return on Sheet1 the sum of quantity of each instrument that have been trade over a period of time. For example: Lets say that 7 over 10 portfolios have trade in Apples Inc Shares, and there have been 7 purchased during the period of time( each bought 1000 shares therefore 7000 overall). However during the period of time some of the 7 investors have sold their shares, Lets says, 2 of them sold their entire portfolio, so 1000 shares each. As a result a final value of 5000 is remained.
What i want to return in sheet 1 is:
Column 1( Cell A1 i will write my ISIN numbr which is also my lookup value)
Column 2: Sum of each instrument bought during the period of time, (Cell B1 7000 Shares)
Column 3:Sum of each instrument sold during the period of time, (Cell C1 2000 Shares)
Column 4 i Need to do the difference between colum 2 and 3, ( Cell D1 5000)
Column 5, 6 and 7 i need to to the same thing as in colum 2,3 and 4, however instead of using the quantity i need to use the HISTORICAL VALUE, and return and aggregate figures like with the quantities.
Can someone please helping me? How should write the formula?
Best regards,
Lorenzo
