Assistance in developing a IF formula that looks at a number of criteria's

[kets23]

Hi

Would be grateful for any assistance in developing a IF formula that looks at a number of criteria's and inputs one of 2 text based on the criteria's.

Thanks in advance

Kets

 

[AlanY]

try this in D2, and copy down

=IF(COUNTIF(E2:I2,"Pass")=5,"Pass",IF(COUNTIF(E2:I2,"Fail")>0,"Fail",""))

 

[kets23]

Hi Alan

Many thanks for your response, have tried and the formula partially works.

My formula range is =IF(COUNTIF(K5:N5,"Pass")=5,"PASS",IF(COUNTIF(K5:N5,"Fail")>0,"Fail",""))

For example, when Cells K5 to N5 is all pass, cell D5 does not get updated with Pass

When there is a fail in any of the cells in K5 to N5, cell D5 gets updated to say Fail only where Fail is selected in any of the Cells in K5 to M5 but not N5, if Fail is selected in N5, Fail is not displayed in D5.

However, if Fail is selected in 2 or more of the cells in K5 to N5, fail is displayed in D5

Do you know why this is?

 

[AlanY]

because your K5 to N5 range only has 4 cells. if that's all required to check then you can change 5 = 4 in the formula to work

 

[kets23]

Many thanks Alan

 

[AlanY]

you're welcome