Excel Formula for multiple conditions

[RaveenaAV]

Hi 

I'm trying to work out a formula for the following conditions in a range of cells, between the options of "Yes", "No" and "Mostly"

1.If all answers "Yes" then "Doing Our Job"

2.If a mixture of "Yes" "Mostly" then "Underperforming"

3.If at least one "No" then "Unacceptable" if not "Underperforming"

Currently I've worked out this formula using IFS function:

=IFS(SUMPRODUCT(--(C4:F24="Yes")),"Doing Our Job", COUNTIF(C4:F24,"No"),"Unacceptable",(COUNTIF(C4:F24,"Yes")+COUNTIF(C4:F24,"Mostly")),"Underperforming")

Is there a better way to do this?

Could you please help me with the same, thanks in advance! 

[#2 · Accepted solution]

@RaveenaAV 

=IFS(
  COUNTIFS(C4:F24,"Yes")=COUNTA(C4:F24),"Doing Our Job",
  COUNTIFS(C4:F24,"No"),"Unacceptable",
  TRUE,"Underperforming"
)

[#3]

Thank you for your reply!
But the above formula seems to return "underperforming" when all the entries are "Yes".

[#4]

@RaveenaAV 

Not in my world.

[#5]

Thank you very much! I had a text column in between thereby the error.

[#6]

@RaveenaAV 

You mean a column with texts that should not be counted?

Then adjust COUNTA().