How can I change the value of one cell based on the values of multiple cells?

[CaitlinStevens]

I have created a spreadsheet to monitor the testing results of lighting. If the word 'Fail' is entered at least once in a range of cells, I would like the word 'Fail' to show in another cell as the overall result.

I have used the formula =IF(COUNTIF(M5:M8,"*Fail*")=COLUMNS(M5:M8),"Fail","Pass") which does seem to work if the word 'Fail' is only listed once in the range, however if the word 'Fail' is listed more than once then the other cell changes to 'Pass' instead.

Can someone please help?

Thank you in advance!

 

[jtakw]

Hi,

Take the COLUMNS(M5:M8) part out, this part is Always =1, so just:

=IF(COUNTIF(M5:M8,"*Fail*"),"Fail","Pass")

ALSO, if you Only have the Single word "Fail" in the range M5:M8, you can take the * (wildcard) out, unless you have Other words mixed in like (e.g. Failed, Failing, Definitely Failed, etc.)

 

[CaitlinStevens]

Hi,

Take the COLUMNS(M5:M8) part out, this part is Always =1, so just:

=IF(COUNTIF(M5:M8,"*Fail*"),"Fail","Pass")

ALSO, if you Only have the Single word "Fail" in the range M5:M8, you can take the * (wildcard) out, unless you have Other words mixed in like (e.g. Failed, Failing, Definitely Failed, etc.)

Wow, thank you so much. This has worked!

I forgot to add that if a cell is blank, this shouldn't be given the value of 'Pass' - it should just remain blank. Is there a way I can incorporate this into your formula please?

 

[jtakw]

You're welcome.

Wow, thank you so much. This has worked!

I forgot to add that if a cell is blank, this shouldn't be given the value of 'Pass' - it should just remain blank. Is there a way I can incorporate this into your formula please?

Do you have formulas in the range M5:M8, or are those Pass/Fail manually entered, again, I'd like to point out if you have Only the word "Fail" in the cells, you don't need the wildcard.

 

[CaitlinStevens]

You're welcome.



Do you have formulas in the range M5:M8, or are those Pass/Fail manually entered, again, I'd like to point out if you have Only the word "Fail" in the cells, you don't need the wildcard.

Thank you, the words 'Pass' and 'Fail' would be entered manually so I have removed the wildcard as suggested. My only problem now is that when the cell is blank, it automatically changes to 'Pass' and should instead just remain blank. I hope that makes sense? Appreciate your help!

 

[jtakw]

This should work:

=IF(COUNTA(M5:M8)=0,"",IF(COUNTIF(M5:M8,"Fail"),"Fail","Pass"))

 

[CaitlinStevens]

This should work:

=IF(COUNTA(M5:M8)=0,"",IF(COUNTIF(M5:M8,"Fail"),"Fail","Pass"))

Oh my goodness, you are a lifesaver!!! Thank you so much, I am honestly so grateful for your help.

 

[jtakw]

You're welcome, thanks for the feedback.