Formula that looks in range and returns Pass if all cells contain Ps

[Jamidd1]

Hi All,

I am looking to adapt the following formula.

Please Login or Register  to view this content.

At the moment this formula looks in the range and returns Pass if all cells contain P and if they are all blank nothing is returned. I was wondering if it was possible to make it if they are all blank no value is returned but if say if only one cell has P in it it returns the value fail.

Thanks

If you need any further information please let me know i will upload a example book if needed.

[JohnTopley]

Try

=IF(COUNTIF(R4:T4,"P")=COLUMNS(R4:T4),"Pass",IF(COUNTIF(R4:T4,"P")=1,"Fail",""))

[Jamidd1]

Thanks John that worked a treat, I have now got a column where an X is also added to the mix, i tried this code but it does not seem to work

Please Login or Register  to view this content.

Have you got any suggestions?

Thanks

[JohnTopley]

I assume an OR condition i.e either P or X present...

=IF(COUNTIF(R4:T4,"P")=COLUMNS(R4:T4),"Pass",IF(OR(COUNTIF(R4:T4,"P")=1,COUNTIF(R4:T4,"X")=1),"Fail",""))

Typo in first submission

[Jonmo1]

If I'm understanding correctly, the conditions are

ALL P = Pass
ALL Blank = nothing
ANY OTHER combination of blanks P or X = Fail

Right?

Try
=IF(COUNTA(R4:T4),IF(COUNTIF(R4:T4,"P")=COLUMNS(R4:T4),"Pass","Fail"),"")

[Tony Valko]

Maybe this array formula**:

=IF(AND(R4:T4="P"),"Pass",IF(OR(R4:T4<>""),"Fail",""))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Jamidd1]

Jonno, your understanding is correct. The code you provided still shows up as fail if all cells are blank.

Tony - This formula seems to do the trick! I will test it on a few more cells and let you know the outcome.

Thanks ALL!

[JohnTopley]

Formulas fail if cell has a space rather blank(Null)

try

=IF(SUM(COUNTIF(R4:T4,{"P","X"})),IF(COUNTIF(R4:T4,"P")=COLUMNS(R4:T4),"Pass","Fail"),"")

[Jamidd1]

Thank you all!!