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

**crazychile** · 10-22-2010, 10:48 AM

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

**daddylonglegs** · 10-22-2010, 10:50 AM

Try

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

**crazychile** · 10-22-2010, 11:33 AM

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

**crazychile** · 10-22-2010, 11:38 AM

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

**shg** · 10-22-2010, 11:45 AM

DLL was just typing too fast:

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

**crazychile** · 10-22-2010, 11:51 AM

Sweeet! That did it! Thanks everyone!!!

**daddylonglegs** · 10-22-2010, 07:28 PM

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?

**suchitra.priya** · 07-18-2017, 04:13 PM

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