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!
I've added a solution with PIVOTBY. The total I'm getting is 9 stocks:
=LET(
agg, PIVOTBY(M[Ticker], M[Trans], M[Units], SUM, 0, 0),
ticker, CHOOSECOLS(agg, 1, 5),
keep, TAKE(agg, , -1),
return, FILTER(ticker, keep > 0),
return
)I believe Apple is the 10th stock. The formula returns a 0 for that stock.
YES!! You did it!! Thanks so much. I don’t know why Apple is returning as a 0, but otherwise it is perfect!! You have no idea how much I have been through to get to this point. I am new at all of this and beginning to think it was an impossible project. 😄