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!
This is just a variation of what's already been shared:
=LET(
tkr, UNIQUE(SORT(M[Ticker])),
amt, LAMBDA(trn,SUMIFS(M[Units],M[Ticker],tkr,M[Trans],trn)),
net, amt("Bought")-amt("Sold"),
FILTER(HSTACK(tkr,net),net)
)Adjust the table/field names as needed. (EDIT: I already adjusted the table name to "M", so it will work with your Stocks.xlsx file.)
A couple of tips/comments:
- the UNIQUE function is significantly faster when the array is sorted first, so it's best to use UNIQUE-SORT instead of SORT-UNIQUE.
- to remove zeros from an array of numeric values, just pass the array to the include argument of the FILTER function... there is no need to perform a logical comparison, such as net<>0, because zeros are already interpreted as FALSE, and all other numeric values are interpreted as TRUE.