Nested AND OR IF function

[Elfi35601]

I'm trying to get a result based on the data shown in three cells. These three cells have formulas in and are referencing fields from different sheets - each cell will either pull through as "pass", "fail" or blank. I'm trying to get a formula that will show "PASS" if ALL 3 cells say "Pass", "FAIL" if ANY of the 3 cells say "Fail" and (I've managed to get this so far, it's this last bit I get stuck on) if ALL of the cells are blank I want the result to be a blank cell.

So far I have: =IF(AND(D11="Pass",E11="Pass",F11="Pass"),"PASS",IF(OR(D11="Fail",E11="Fail",F11="Fail"),"FAIL",IF(AND(LEN(D11)=0,LEN(E11)=0,LEN(F11)=0),"","")))

The AND and the OR work well, but it's the last function. Maybe I need to change the order? Also I'm not sure what to put for 'FALSE" for the last function - it depends on the previous AND or OR result. Finally, I had tried ISBLANK but of course that didn't work as there is a formula in each cell, so I used LEN instead.

Any help appreciated!

 

[Fluff]

What if one cell is Pass & the other two are blank?

 

[Elfi35601]

Thank you! Then I want "PASS" - essentially, I want the blank cells ignored - if only one person has 'scored' then that's the only cell to matter. So there might be up to three responses, but might be only one or two.

 

[Elfi35601]

...but as soon as any one person scores 'Fail" (no matter if there are 1, 2 or 3 people scoring) then the result MUST be 'FAIL" - "PASS" can only occur where there are only "Pass" (and blanks potentially)

 

[Fluff]

Ok, how about

=IF(COUNTIFS(D11:F11,"Fail"),"Fail",IF(COUNTIFS(D11:F11,"Pass"),"Pass",""))

 

[Elfi35601]

Bloody hell! That's amazing, and so much more simple Works perfectly, thank you very much

 

[Fluff]

You're welcome & thanks for the feedback.