Forum Discussion

RaveenaAV's avatar
RaveenaAV
Copper Contributor
May 19, 2022
Solved

Excel Formula for multiple conditions

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! 

Formulas and Functions
  • Detlef_Lewin's avatar
    Detlef_Lewin
    May 19, 2022

    RaveenaAV 

    =IFS(
  COUNTIFS(C4:F24,"Yes")=COUNTA(C4:F24),"Doing Our Job",
  COUNTIFS(C4:F24,"No"),"Unacceptable",
  TRUE,"Underperforming"
)
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    May 19, 2022

    RaveenaAV 

    =IFS(
  COUNTIFS(C4:F24,"Yes")=COUNTA(C4:F24),"Doing Our Job",
  COUNTIFS(C4:F24,"No"),"Unacceptable",
  TRUE,"Underperforming"
)
    • RaveenaAV's avatar
      RaveenaAV
      Copper Contributor
      May 19, 2022
      Thank you for your reply!
      But the above formula seems to return "underperforming" when all the entries are "Yes".

Resources