IF SUMPRODUCT with an exception question

[Dadof2]

I have the following formula to give me an alert every time the formula catches a bond rating that has "Ca", "Caa" or "D" in the cell. The problem is that many of the reference cells have #N/A Invalid Security in them and thus have a "D" since that letter is in the word 'Invalid'.

How do I modify this formula to exclude all cells that have "Invalid" in the cell??


=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({"Ca","Caa","D"},C5)))))>0,"Alert","")

Thanks Again!!

Mark

[Ace_XL]

If its always a Capital D use FIND instead, which is case sensitive

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({"Ca","Caa","D"},C5)))))>0,"Alert","")

[bartelba]

Hi,

Try this one.

Bas