formula for evlauating cells for specific text

Ultimately I am trying to evaluate the D column to see if it contains any of the words: Missed, Hold, or By-passed, but I also want to evaluate the B column to see if it contains the number 1.
If the evaluation finds any of these, in either column B or D, I want it to display as "evaluate" whereas if none of these are found, I want it to display "no concern"

I have tried the formula below, with very little success.

=IFS(ISNUMBER(SEARCH("hold",D3:D3000)),IF(ISNUMBER(SEARCH("1",B3:B3000)),IF(ISNUMBER(SEARCH("missed",D3:D3000)),IF(ISNUMBER(SEARCH("by-passed",D3:D3000))"evaluate"," no concern")))


Your help is greatly anticipated!

Try

=IF(SUM(COUNTIF(D3:D3000,{"hold","missed","by-passed"}),COUNTIF(B3:B3000,1)),"evaluate","no concern")

Try it like this:

=IF(OR(COUNTIF(D3:D3000,"*hold*"),COUNTIF(D3:D3000,"*missed*"),COUNTIF(D3:D3000,"*by-passed*"),COUNTIF(B3:B3000,1)),"evaluate","no concern")

The asterisk is a wildcard character, so the formula is looking for any characters before or after "hold", "missed" etc.

Hope this helps.

Pete

I appreciate the attempt, but I am only getting a response of "evaluate" , even when there is clearly no evidence of having the words or the Number 1 in either cell B or D

Anyone else?

Thanks for the response. I get a "evaluate" when there is a 1 in the B column, but if I remove the 1 and add any of the words into the D cell, it does not pick up on it to indicate "evaluate"

Please keep trying!!!

Originally Posted by MJam

Thanks for the response. I get a "evaluate" when there is a 1 in the B column, but if I remove the 1 and add any of the words into the D cell, it does not pick up on it to indicate "evaluate"

Please keep trying!!!

Please Login or Register  to view this content.

This also needs to be entered as an array formula.

Maybe it's time for you to attach a sample Excel workbook, so we can try different formulae out with your actual data.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Pete