Forum Discussion
Excel Sumifs
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!
HansVogelaar, Patrick2788 and DJclements have all put a different formula on the “Dashboard” sheet in my file, each with the same results. Thank you all for your time. A study of this will teach me a lot. The problem is each result shows all stocks bought and sold and does not subtract sold from bought for each individual stock returning only the stocks still owned. There are only 10 stocks still owned (see Dashboard2). Thanks again to all of you.
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!