+ Reply to Thread
Results 1 to 8 of 8

How to make a cell display "FAIL" when range of cells is FAIL

  1. #1
    Registered User
    Join Date
    10-22-2010
    Location
    Midwest, USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    How to make a cell display "FAIL" when range of cells is FAIL

    This should be simple but I havent been able to find it in Excel help. I also tried conditional formatting with no success.

    All I want to do is have a cell (lets say, A1) display FAIL if any cell within a range (lets say A2:A6) contains FAIL.

    In addition if I could get it to display PASS if ALL of the cells in the rancge contain PASS.

    Thanks in advance,

    crazychile

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    re: How to make a cell display "FAIL" when range of cells is FAIL

    Try

    =IF(COUNTIF(A2:A6,"Pass")=ROWS(A2:A6),"Pass",COUNTIF(A2:A6,"Fail"),"Fail",""))
    Audere est facere

  3. #3
    Registered User
    Join Date
    10-22-2010
    Location
    Midwest, USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    re: How to make a cell display "FAIL" when range of cells is FAIL

    Thanks, but that comes up as an error. Will this also take into account that it's OK to have a cell in the range as blank?

    Thank you,
    crazychile

  4. #4
    Registered User
    Join Date
    10-22-2010
    Location
    Midwest, USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    re: How to make a cell display "FAIL" when range of cells is FAIL

    Sorry, to clarify.....If all of the cells contain pass, then it should display pass. If any cell is FAIL then its fail, and blanks are ok when considering the fail

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,651

    re: How to make a cell display "FAIL" when range of cells is FAIL

    DLL was just typing too fast:

    =IF(COUNTIF(A2:A6,"Pass")=ROWS(A2:A6), "Pass", IF(COUNTIF(A2:A6,"Fail"), "Fail", ""))
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    10-22-2010
    Location
    Midwest, USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    re: How to make a cell display "FAIL" when range of cells is FAIL

    Sweeet! That did it! Thanks everyone!!!

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: How to make a cell display "FAIL" when range of cells is FAIL

    Thanks for the correction shg

    To answer your question, the formula returns PASS only if there are 6 passes. Any other combination of PASS/FAIL blank will return FAIL if there's one or more "FAILS", otherwise blank.....so that means that 5 PASSes and one blank just returns a blank - is that OK?

  8. #8
    Registered User
    Join Date
    07-18-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    1

    Re: How to make a cell display "FAIL" when range of cells is FAIL

    hi

    I need of the similar formula checking the below 4 condition and update

    If all the test step in Range (B5:B9) is Pass then update Cell B4 as Pass,
    If any of test step in Range (B5:B9) is Fail then update Cell B4 as Fail,
    If any of test step in Range (B5:B9) is Pass with a Defect then update Cell B4 as Pass with a Defect
    If any of test step in Range (B5:B9) is In Progress then update Cell B4 as In progress

    Thanks in advance
    suchitra.S

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1