Help with IF function

[Michelle]

Am trying to create a formula that will tell me: if the range of cells has
the single letter "R" or the single letter "T" in the individuals cells, it
will return the word "Pass", or otherwise will return the word "Fail".

Thanks,

 

[Peo Sjoblom]

=IF(SUM(COUNTIF(A1:A20,{"R";"T"}))>0,"Pass","Fail")


replace A1:A20 with your range


--


Regards,


Peo Sjoblom

 

[T. Valko]

I'm assuming you mean if *any* cell in the range contains a R or T.

Assume the range in question is A1:A10...

=IF(SUM(COUNTIF(A1:A10,{"R","T"})),"Pass","Fail")

If *every* cell in the range is empty the result of the formula will be
Fail. Don't know if that's what you want.

 

[Michelle]

Thank you for your replies, but I obviously didn't make myself clear. Am
trying to create a formula where: if the range of cells has the letter "R"
or "T" in the cells, then the result will be "pass", but if any cell is blank
or has a different letter in it, then the result will be "fail".

Thanks,

 

[T. Valko]

OK, just need a slight modification:

=IF(SUM(COUNTIF(A1:A10,{"R","T"}))=ROWS(A1:A10),"Pass","Fail")

 

[Rick Rothstein \(MVP - VB\)]

This array-entered** formula appears to work...

=IF(COUNT(--FIND(A1:A10,"RT"))=COUNTA(A1:A10),"Pass","Fail")

** Commit the formula using Ctrl+Shift+Enter, not just Enter by itself

Rick

 

[Michelle]

Thank you, but the formula doesn't give me the correct result. If I have
either "T" or "R" in all the cells, it gives me a "Fail" result, when I need
a "Pass" result.

 

[Rick Rothstein \(MVP - VB\)]

Don't use this formula... it has a flaw in it.

Rick

 

[Michelle]

Works perfectly! Thank you all for your help.

 

[Rick Rothstein \(MVP - VB\)]

No it doesn't... there is a flaw in it. It will show a pass if there is the
same number of empty cells as there are characters that are not an R or T.

Rick

 

[Michelle]

Ok thanks for the warning.... was looking good!

 

[T. Valko]

It works for me. Post the *exact* formula you used.

If all cells contain a R or if all cells contain a T or if all cells contain
either a R or a T the formula returns Pass. If there are any empty cells or
any entry other than a R or T the formula returns Fail.

 

[Teethless mama]

It works on my machine.

No it doesn't... there is a flaw in it. It will show a pass if there is the
same number of empty cells as there are characters that are not an R or T.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Try filling in A1:A10 with this combination...

A1: <Leave empty>
A2: X
A3:A10 Any combination of Rs and Ts

for one of many ways to make the formula return a Pass when it shouldn't.

It appears that FIND will find any string of characters inside the empty
string. With A11 empty,

=FIND(A11,"Any text")

will return 1 as an answer. You can also see this without referencing an
empty cell...

=FIND("","Any text")

Anyway, with or without the double unary, because of this, the COUNT
function in my formula counts a hit here but COUNTA doesn't. So, a single
non-hit by COUNTA (the X in my above example) is balanced out by my COUNT
function's hit on the empty cell.

Rick