Formula using IF? Vlookup? Lookup? I'm not sure.
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!!! |
Formula using IF? Vlookup? Lookup? I'm not sure.
=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) "CJ" wrote in message ups.com... 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!!! |
Formula using IF? Vlookup? Lookup? I'm not sure.
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 Phillips wrote: =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) "CJ" wrote in message ups.com... 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!!! |
Formula using IF? Vlookup? Lookup? I'm not sure.
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! CJ wrote: 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 Phillips wrote: =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) "CJ" wrote in message ups.com... 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!!! |
All times are GMT +1. The time now is 04:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com