Forum Discussion
Solved
Excel Sumifs
I need a formula to return how many shares of each stock I still own; Subtract # Sold from # Bought for each ticker and return the number remaining of each stock sorted by ticker. No return if the r...
The only issue was, in your sample table, all "Units" amounts were entered as positive numbers, so all solutions were written to subtract "Sold" from "Bought"; however, in your actual Stocks.xlsx data table, "Sold" amounts were entered as negative numbers. Simply modify any one of the suggested formulas to add "Sold" to "Bought" instead of subtracting.
The GROUPBY method also becomes significantly easier:
=LET( grp, GROUPBY(M[Ticker],M[Units],SUM,0,0), FILTER(grp,TAKE(grp,,-1)>0) )Or, to include stocks with a negative balance:
=LET( grp, GROUPBY(M[Ticker],M[Units],SUM,0,0), FILTER(grp,TAKE(grp,,-1)) )Also, I noticed the "Trans" column contains some "Dividend" entries (aside from just "Bought" and "Sold"). If these entries were to have amounts in the "Units" column that needed to be excluded, you can filter them out using the optional [filter_array] argument:
=LET( grp, GROUPBY(M[Ticker],M[Units],SUM,0,0,,M[Trans]<>"Dividend"), FILTER(grp,TAKE(grp,,-1)>0) )Cheers!
With everyones help I was able to use a supplied formula to extract stocks I own from all “Bought” and “Sold”. I have added more information and now would like to set up some charts to see the results. Here are my column headings. It would be fun to have, for instance, a pie chart showing the % or # of stocks in each Industry and or the Total Gain % by Stock or industry.
If you have signed into my file before, I updated the “Dashboard” and renamed the sheet with all the formulas to “Formulas” I would like the charts to appear on the “Dashboard” sheet. Or, I can try with some formatting and formula help. Thanks again to all and if I am overstepping my bounds by asking for more help, please let me know.