Help with Excel formula, IF

[raf]

Im trying to create IF formula, from A1 to M1.
A1 to M1 could have a value of : "N/A", "PASSED"or any value
now what im trying to do is to have a formula that will show O1, If the user gets all passed fields, Or at lease one failed.
Formula logic should look like:

IF A1: M1 = PASSED or N/A then O1 = Passed
IF A1:M1= N/A or Any value then O1 = Failed


Passed if A1:M1 is just showing N/A or Passed, other than that is should show FAILED.

Thanks in advance!

 

[Hui]

Raf

I think this will do what you want
=IF((COUNTIFS(A1:M1,"PASSED")+COUNTIFS(A1:M1,"N/A"))>=12,"Passed","Failed")

 

[raf]

Raf

I think this will do what you want
=IF((COUNTIFS(A1:M1,"PASSED")+COUNTIFS(A1:M1,"N/A"))>=12,"Passed","Failed")

Yeh your code worked, but, Im receiving Passed:with val value under K1, please see attached.

 

[Hui]

In your original post you said there could be one failed ?
The formula
=IF((COUNTIFS(A1:M1,"PASSED")+COUNTIFS(A1:M1,"N/A"))>=12,"Passed","Failed") achieves that

If you need 13 values of Passed or N/A change the formula to
=IF((COUNTIFS(A1:M1,"PASSED")+COUNTIFS(A1:M1,"N/A"))=13,"Passed","Failed")

 

[raf]

In your original post you said there could be one failed ?
The formula
=IF((COUNTIFS(A1:M1,"PASSED")+COUNTIFS(A1:M1,"N/A"))>=12,"Passed","Failed") achieves that

If you need 13 values of Passed or N/A change the formula to
=IF((COUNTIFS(A1:M1,"PASSED")+COUNTIFS(A1:M1,"N/A"))=13,"Passed","Failed")

looks Great! thank you so much Hui!

 

[deepak.gupta@logica.com]

Hi Hui, Any advantages of using COUNTIFS over COUNTIF in this case?

 

[Khalid NGO]

Hi Hui, Any advantages of using COUNTIFS over COUNTIF in this case?

Deepak
As there is only one criteria you can go with COUNTIF with same result.

 

[deepak.gupta@logica.com]

ok. Thanks.

 

[Hui]

As Khalid said, there is no advantage

I had tried to incorporate the two conditions together in one Countifs() function, before realising that the logic was incorrect, hence the Countifs

 

[deepak.gupta@logica.com]

Thanks Hui.

 

[raf]

In your original post you said there could be one failed ?
The formula
=IF((COUNTIFS(A1:M1,"PASSED")+COUNTIFS(A1:M1,"N/A"))>=12,"Passed","Failed") achieves that

If you need 13 values of Passed or N/A change the formula to
=IF((COUNTIFS(A1:M1,"PASSED")+COUNTIFS(A1:M1,"N/A"))=13,"Passed","Failed")

Hi Hui,

What If I want to add this:

IF A1:M1="","",""

so it wont show Any value if the specific row is blank?

 

[Hui]

I think you need:
=IF(COUNTA(A1:M1)=13,IF((COUNTIFS(A1:M1,"PASSED")+COUNTIFS(A1:M1,"N/A"))>=12,"Passed","Failed"),"")