Need Formula for P/L for sale of a stock

[dlcartin]

I have a Table of stocks that I buy & sell. I need a Formula that will subtract my 'Sell Price' from my 'Buy Price' showing me any P/L that occurred once it has been sold (I don't do multiple buys of a stock before finally selling it, however I may do so in the future). Example: I bought TSLA on APR 1st and on MAY 1st I decided that it wasn't for me, so I sold it. I did, however, buy a couple of other stocks within that time frame, so there will be any # of rows in between (this will also occur as I buy & sell stocks in the future). In the Table below it shows when I bought TSLA (Row #5) & sold it (Row #8). I want to show in column 'J' (on the corresponding  row of the sale) the value of my P/L when it was sold. I hope that I worded this properly. My knowledge of Excel is on a 'Moderate' level and no Formula (Xlookup, Direct, Match, etc) that I've tried has worked. Thank you very much for any help that you may provide!! (BTW, Column 'A' is left blank intentionally.)

[#2]

@dlcartin

You can try below formula in column K
=IF(E3="Sell","",XLOOKUP(C3&"Sell",$C$3:$C$9&$E$3:$E$9,$I$3:$I$9,0))

[#3]

I edited my post to give a (hopefully) better understanding of what I presently have vs what I need. You state to enter your Formula into COL 'K', nut I need the results in COL 'J', so that's where I placed it (Cells J3:J9). After entering your Formula the above pic shows your formula's results. Thank you for being so kind as to reply to my post even though it's not the results that I want/need. Have a Blessed Life and thank you, again.
P,S, Perhaps I'll able to modify your Formula to get the results I need.

[#4]

https://www.msofficeforums.com/excel/51695-running-total-dependant-another-cells-contents-if.html#po...


running total by group?

[#5]

Forgive me for taking so long to reply, but Formulae such as this take me a while to understand (and, I must admit that I'm still confused with it). At first glance, this appeared not to be what I was looking for. After thinking on this for a while a 'Running Total by Group' would seem to fit regardless of the number of buys on a stock before it is sold. Though I still do find it rather hard to understand and follow the Formula posted by ArviLaanemets. I tried to use it, but without a more simpler explanation of it, I don't believe that I'll be able to use it. I thank you for being so kind as to use your valuable time to reply to my post. Have a Blessed Life!
P.S.
This brings up the question of how this Formula would work if, after selling a stock, I buy it again months later. Since I use the stock's Symbol and not a unique identifier would this Formula also take into account an earlier 'Buy - Sale' group when it shouldn't? Again, thank you!

[#6]

I do not understand your request yet.
if you upload a work book file rather than .png screenshot and expected result,I guess more guys may be interested in this question.

[#7]

I uploaded a copy. It's titled 'MM Acct.xlsx'. Hope this helps. Thanks to everyone!!

[#8]

Do you need to calculate the P/L when a stock has not sold out all quatantity?
In other words,buy 3 and sell 1 still hold 2.

If so,you need to consider

Cost of Goods Sold
https://www.investopedia.com/terms/c/cogs.asp

[#9 · Accepted solution]

@dlcartin 

Perhaps

=IF([@UNITS]>0, 0,
 IF( [@SYMB]="", "",
    SUMPRODUCT(
        ([@DATE] >= [DATE] )*
        ([@SYMB] = [SYMB])*
        -[UNITS]*
        [$/Share]
    )
 ) )

[#10]

@dlcartin 

In addition, if you'd like to deduct possible fees

=IF([@UNITS]>0, 0,
 IF( [@SYMB]="", "",
    SUMPRODUCT(
        ([@DATE] >= [DATE] )*
        ([@SYMB] = [SYMB])*
        -[UNITS]*
        [$/Share]
    ) -
    SUMPRODUCT(
        ([@DATE] >= [DATE] )*
        ([@SYMB] = [SYMB])*
        [FEE(S)]
    )
 ) )

and cosmetic, it's better to apply "comma style" format to P/L column