Formula using IF? Vlookup? Lookup? I'm not sure.

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

In summation, here is what I need!!
if ANY fails exist, then read Fail
if ANY blanks exist, then read blank
if ALL entries = pass, then read pass.

I have cells G20:G22 that can contain either Pass, Fail, or ""
I have a cell H18 that I want to show me the following.

IF *All* G20:G22 = "Pass"
Then "Pass"
ELSE
IF *Any* G20:G22 = "Fail"
Then "Fail"
Else
""
END IF

I'm sure that isn't the correct syntax in code, but I'm trying to
explain it. Here are examples of what I need....

****************
G20 =""
G21 =""
G22 =""
then H18 =""

****************
G20 ="Pass"
G21 =""
G22 =""

then H18 =""

****************
G20 ="Pass"
G21 ="Pass"
G22 =""

then H18 =""

****************
G20 ="Fail"
G21 ="Pass"
G22 =""

then H18 ="Fail"

****************
G20 =""
G21 ="Fail"
G22 =""

then H18 ="Fail"

****************
G20 ="Pass"
G21 ="Pass"
G22 ="Pass"

then H18 ="Pass"



Thanks for your help!!!
 
=IF(COUNTIF(G20:G22,"Pass")=COUNT(G20:G22),"Pass",IF(COUNTIF(G20:G22,"Fail")
0,"Fail",""))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
=IF(COUNTIF(G20:G22,"Pass")=COUNT(G20:G22),"Pass",IF(COUNTIF(G20:G22,"Fail")
0,"Fail",""))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
Bob, that helped a lot.

Can someone add on to this per the examples I listed 1st and 2nd in the
original post? Basically, in situations in which not all options are
marked I need it to read 'Incomplete' or "ToDo". In order for it to
read Pass, ALL fields must read Pass. In order for it to read Fail,
only one of the fields need to read Fail. All other situations should
read ToDo.

Thanks in advance for your assistance!!
 
Bob, that helped a lot.

Can someone add on to this per the examples I listed 1st and 2nd in the
original post? Basically, in situations in which not all options are
marked I need it to read 'Incomplete' or "ToDo". In order for it to
read Pass, ALL fields must read Pass. In order for it to read Fail,
only one of the fields need to read Fail. All other situations should
read ToDo.

Thanks in advance for your assistance!!
 
I figured this out on my own.

Here it is if it'll help anyone else.

=IF(COUNTIF($G$20:$G$28,""),"To Do",IF(COUNTIF($G$20:$G$28,"Fail")
0,"Fail","Pass"))


I took out the 2nd Count and replaced it with an if true "To Do", then
if false and fail > 0 read fail if not > 0 then read pass.

It works exactly how I wanted! YEAH!
 
Back
Top