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!
Patrick, this looks like exactly what I want but when I copied and pasted the formula in my file, all it shows in the cell is the formula. It does not calculate or return as yours did. I’m very new at this and using the Excel 365 for business online. Thanks for your effort. Do you know where I can find someone who can work on my file online and fix the problem for me?
Make sure that
- The cell with the formula is not formatted as Text.
- The 'Show Formulas' button on the Formulas tab of the ribbon is not highlighted.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
When I paste a formula into a cell on the “Dashboard” sheet it only shows the formula, does not do a calculation or show a result. The “Show Formulas” button is not highlighted and the cell is formatted as “General”. Since the data is downloaded from “Stocks” data a copy does not have the data in it, only the online version shows the data.
I added the formula to 'dashboard'. For your workbook the table name had to be updated:
=LET( values, IF(M[Trans] = "Sold", -M[Units], M[Units]), agg, GROUPBY(M[Ticker], values, SUM, , 0), FILTER(agg, TAKE(agg, , -1) <> 0) )
