Need IF Statement to check text

Hello ! Happy new year!
Looking for some help to write two IF statements to check for text in a range of cells and then return a value. Attached is my sample excel file and the two questions I have are shown on the file... Sample IF statement.xlsx
I tried a few methods found online but none were exactly working...

Note that I have lookup formulas where the #N/A are shown on the excel file, will the IF statement still check or work for that?
THANK YOU SO MUCH!



IF Function to check if column A to N does not contain any Yes or Yes.., return 1, otherwise show blank.

If column A to N contains ONLY Yes or Yes.., return 1, otherwise show blank.

In cell O2:

Formula:

Please Login or Register  to view this content.

In cell P2:

Formula:

Please Login or Register  to view this content.

Originally Posted by etaf

=IF(COUNTIF(A2:N2,"*"&"yes"&"*")=0,1,"")
does not contain a yes



=IF(COUNTIF(A2:N2,"*"&"yes"&"*")>0,1,"")
does contain a YES

what does ONLY contain a yes - what other content is allowed

Thanks! For this statement, I need it to show a 1 when there is "Yes" or "Yes," or " Yes..." there could be "Unsure" "Maybe" but I don't care as long as there are Yes or Yes,... I tried your formula it did not work when there are "No" and "Yes" both in the range.

Nothing in your instructions talking about a No. Can add another piece to that:

Formula:

Please Login or Register  to view this content.

Originally Posted by etaf

can you explain / give examples

I am just counting the range to see if there is a YES in the contents anywhere -
so

is a yes

will also see as a yes


Unsure or Maybe - will not be counted as no YES in the cell

row 6 in your example has a
NO in A6
YES in D6

so as i'm only counting YES - ignoring everything else

If there is a NO anywhere in the range - then that counts as a blank - regardless of YES

may need some expected results in your sample and explanation why

I just built out more results in my sample and it looks like it is working!!!! My brain is fried so excuse any confusions I might have caused. The actual data set I am working on is SO much larger than the sample. I think both formulas you sent me works well! Thank you!!

thank you! I think I got the answer I needed.

Originally Posted by thomglea

In cell O2:

Formula:

Please Login or Register  to view this content.

In cell P2:

Formula:

Please Login or Register  to view this content.

thank you for your response. I think I got it!

Originally Posted by etaf

you are welcome

Hi - I have one additional question. I am thinking to add a column to count the number of Yes or yes...that showed up in the range of cells so it returns a numeric value. If yes or yes...showed up three times, I want to see a 3 in my column. what would that formula be? thank you again!!

Originally Posted by etaf

pretty much the same one, that we are using to count the cells with a YES - and then using a =0 >0 to show true / false
so
=COUNTIF(A2:N2,"*"&"yes"&"*")

worked like magic! thank you so much. I added reputation for your great work!

Originally Posted by HansDouwe

Thanks for your feedback. Glad you found the right formula.

Please consider adding reputation to all helpers, who you think they deserved because of the help provided.
If you want to, please click *Add reputation at the bottom left of their answers.

Yes I did already. thanks for your response!!! you guys made my day. This forum is an amazing resource!