Excel Sumifs

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.

Thanks so much to everyone who contributed.  There were several different formulas offered and they all returned the correct results.  This was very helpful and will give me lots to study and learn as it is all new to me and, needless to say, a bit overwhelming. BRAVO

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.

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.

I am not sure which option I prefer

=LET(
    holding, GROUPBY(
        DemoTbl[Ticker],
        IF(DemoTbl[Transaction] = "Bought", 1, -1) * DemoTbl[Units],
        SUM,  ,  0
    ),
    FILTER(holding, TAKE(holding, , -1) > 0)
)

or

=LET(
    ticker, SORT(UNIQUE(DemoTbl[Ticker])),
    subtotals, SUMIFS(
        DemoTbl[Units],
        DemoTbl[Ticker], ticker,
        DemoTbl[Transaction], {"Bought", "Sold"}
    ),
    holding, BYROW({1, -1} * subtotals, SUM),
    FILTER(HSTACK(ticker, holding), holding > 0)
)

You can use GROUPBY to rollup the totals. The formula would be a bit shorter if there was no need to remove entries with 0:

=LET(
    values, IF(DemoTbl[Transaction] = "Sold", -DemoTbl[Units], DemoTbl[Units]),
    agg, GROUPBY(DemoTbl[Ticker], values, SUM, , 0),
    FILTER(agg, TAKE(agg, , -1) <> 0)
)

there are a few options

you could use SUMIFS

=SUMIFS( [units], [Ticker], A1, [Trans], "Bought" ) - SUMIFS( [units], [Ticker], A1, [Trans], "Sold" )

you could do a direct calculation using SUMPRODUCT or something similar like:

=SUMPRODUCT ( [units]* IF([Trans]="Bought", 1, -1)*([Ticker]=A1) )

you could also just create a helper column that converts the units to +/- values and then do a single SUMIF or even a pivot table.

Try

=LET(tickers, SORT(UNIQUE(A2:A19)), remaining, SUMIFS($C$2:$C$19, $A$2:$A$19, tickers, $B$2:$B$19, "Bought")-SUMIFS($C$2:$C$19, $A$2:$A$19, tickers, $B$2:$B$19, "Sold"), combined, HSTACK(tickers, remaining), FILTER(combined, remaining>0))