COUNTIFS and SUMIFS with a condition to exclude rows whose value is in another range

Hi everyone - looking for some guidance with a formula I'm trying to write.

I have a portfolio sheet with multiple rows and the following ranges (among others, but these are the relevant ones):
rngTicker - ticker code / unique ID for the stock
rngPosition - whether that ticker is long or short (or has no position "=None")
rngValue - P&L for each ticker at the current date

In that sheet, I count the number of profitable trades in a formula like this:
=SUM(COUNTIFS(rngPosition,{"Long","Short","None"},rngValue,">0"))

I also sum the total profitable value of tickers with positions like this:
=SUM(SUMIFS(rngValue,rngPosition,{"Long","Short","None"},rngValue,">0"))
... and the non-profitable value has the condition "<0".

In another sheet (called Control) is a list of tickers to exclude from the P&L calculations - this is rngExclude.

I can count the number of values in rngTicker that are also in rngExclude with this formula:
=SUMPRODUCT(--(ISNUMBER(MATCH(rngTicker,rngExclude,0))))

But what I cannot work out is how to count and sum these ranges with the added condition that the ticker in rngTicker in the portfolio sheet does not exist in the rngExclude in the control sheet.

Any guidance on how to combine these would be appreciated. Thank you in advance.

MM.

it sounds like you are have ticker numbers ??? if so I would wonder if adding in a minus function going against the ticker numbers that match in both but are under the exclude in the control tab.
Purely a guess since there is no workbook uploaded and it is build a formula by guessing, it appears you have multiple table names which can add to the confusion since some of us would have to see the table names and ranges.
BTW, we value your privacy but a location of Earth isn't helpful since different locals have different excel parameters, for example they treat dates differently and different delimiters.

Thanks Sam ... tickers are unique stock market codes; it doesn't really matter what they are, only to note that they are unique identifiers for a record. I can't upload the actual file but I will create a small sample file and send it tomorrow to make it easier (I too dislike it when folks do that; I should know better - sorry).

Sample file attached for reference.

I'm having a bit of trouble looking back and forth between your post #1 and your uploaded workbook. For example, your sum/countifs rngPosigion matches the formula you have in C3 through C6 so that matches. But your second formula, the sum/sumifs rngValue which in your name manager refers to columns D and E but your formula points that portion at column B though the rest matches (rngPosition matches col A). So when you mention that you want to exclude the control tickers from the P&L formula I'm having trouble seeing which formula you are using for the P&L calculations.

I believe a formula can be written to exclude those but right now I'm not seeing, when I use trace dependents, anywhere where the values in col F of portfolio are being used.
Right now only columns A, B and C in the portfolio tab are being used.

So, if you are wanting to remove the values from, for example, the long profit count in cell C3, simple way, for now that is, I just put a vlookup in col H
=IFERROR(VLOOKUP(F12,Control!$D$5:$D$10,1,FALSE),"")
dragged down, then altered the sum countif in C3 to no longer count those values in col B represented by their tickers from col F (using the vlookup in co H) like so...
=SUM(COUNTIFS($A:$A,{"Long","Short","None"},$B:$B,">0",H:H,""))

now if that is the direction you are wanting to go, I believe a more streamlined version can be developed.

Hi Sam. Thank you for taking the time to respond but in the end I didn't come up with a built-in function combo that could do what i needed so I wrote a UDF and did it in VBA, which is working perfectly. Probably too specific an issue.

Thanks again.
MM

Glad you got it resolved, sorry I couldn't help more. Thank you for the rep though!!