Sight Change

[Front]

I have a formula that works great, but looking to make a slight change for another purpose. Here is the formula

=IF(SUMPRODUCT(ISNUMBER(SEARCH($AF$4,Table134[Stock Symbol]))*(Table134[Profit / Loss]<>""))=0,"NA",SUMIFS(Table134[Profit / Loss],Table134[Stock Symbol],"*"&$AF$4&"*"))

This formula will sum up the cells if the word in AF4 is found in the column Table134[Stock Symbol]. That was great for another calculation, but for this one it does not work. For example if I want to see my performance for stock M, if I type in "M" in AF4 it will give stats for any stock symbol with an M in it. Can someone please recommend a change so it is an exact match instead of just found in the cell?

 

[jasonb75]

This will do it

=IF(SUMPRODUCT(ISNUMBER(MATCH($AF$4,Table134[Stock Symbol],$AF$4,0))*(Table134[Profit / Loss]<>""))=0,"NA",SUMIFS(Table134[Profit / Loss],Table134[Stock Symbol],$AF$4))

 

[Front]

Thank you! I have 6 formulas to do this to. I was able to see what you did and fix 3 more of them. However, 2 of them are still not working. Would you help with this one? I am still going to work on the final one for a bit.
Orginal formula

=IF(SUMPRODUCT(ISNUMBER(SEARCH($AF$4,Table134[Stock Symbol]))*(Table134[Profit / Loss]<>""))=0,"NA",COUNTIFS(Table134[Stock Symbol],"*"&$AF$4&"*",Table134[Profit / Loss],">0")/(SUMPRODUCT(ISNUMBER(SEARCH($AF$4,Table134[Stock Symbol]))*(Table134[Profit / Loss]<>""))))

I tried making it into this, but it did not work.

=IF(SUMPRODUCT(ISNUMBER(MATCH($AF$4,Table134[Stock Symbol]))*(Table134[Profit / Loss]<>""))=0,"NA",COUNTIFS(Table134[Stock Symbol],$AF$4,Table134[Profit / Loss],">0")/(SUMPRODUCT(ISNUMBER(MATCH($AF$4,Table134[Stock Symbol]))*(Table134[Profit / Loss]<>""))))

 

[Front]

Solved this one! Had to rewrite the last part of the formula.

 

[Front]

Okay last one is too complicated for me. This is my longest formula. I have broken it down into several part to see where the issue is. This is the section that I cannot seem to fix. If I change it to match it does not work.

Any assistance would be appreciated so I can change it from any match to exact match

=SUMPRODUCT(--ISNUMBER(SEARCH($AF$4,Table134[Stock Symbol])),--(Table134[Close Price Per Contract / Share]<>""),Table134[ROC %],Table134[Reserve Requirement])

 

[Front]

It took a few hours but I got it. Instead of using ISNUMBER(MATCH, I went with EXACT and it worked! Thanks again for the assistance.