Help to write a statement that if cells meet certain criteria it returns complete else not complete

[jl3782]

Greetings all - I have this formula below where i'm trying to write it to say:

If a column shows "pendiente"(pending), OR "No Asignado" (Not assigned), it returns "Not Complete", else Complete.  If all the columns show pendiente/No asignado then it returns "Not complete", excellent! However, if it's outside of that criteria it should return "Complete", but it returns a FALSE error and i can't figure out why.

What am I missing?

=(IF(AND(S2="Pendiente",T2="Pendiente",U2="Pendiente",V2="Pendiente",W2="Pendiente",X2="Pendiente",Y2="Pendiente",Z2="Pendiente",AA2="Pendiente",AB2="Pendiente",AC2="Pendiente",AD2="Pendiente",AE2="Pendiente",AF2="Pendiente",AG2="Pendiente",AH2="Pendiente"),IF(OR(AI2="Pendiente",AI2="No Asignado"),IF(OR(AC2="Pendiente",AI2="No Asignado"),"Not Complete","Complete"))))

[#2]

@jl3782 

Hello, this formula is quite a complicated and long one. I suggest you simplicity your formula to achieve what you want.

[#3]

@jl3782 

Logic of calculation is defined only partially. If transform the formula

=IF(
   AND(
      S2="Pendiente",
      T2="Pendiente",
      U2="Pendiente",
      V2="Pendiente",
      W2="Pendiente",
      X2="Pendiente",
      Y2="Pendiente",
      Z2="Pendiente",
      AA2="Pendiente",
      AB2="Pendiente",
      AC2="Pendiente",
      AD2="Pendiente",
      AE2="Pendiente",
      AF2="Pendiente",
      AG2="Pendiente",
      AH2="Pendiente"
   ),
   IF(
      OR(AI2="Pendiente",AI2="No Asignado"),
      IF(
         OR(AC2="Pendiente",AI2="No Asignado"),
         "Not Complete",
         "Complete"
      )
   )
)

you see that full logic

if condition then option1 else option2 

is defined only for very inner IF. For others that's only first option defined, by default it'll be returned FALSE as second option

if condition then option1 else FALSE 

In general formula could be simplified (e.g. COUNTIF instead of forst AND, etc), but that's not critical, first the logic is to be defined correctly.

[#4]

@Sergei Baklan If i am following you correctly, you're saying that the IF statements with the OR do not have a condition to meet so it returns false. I am not sure how to wrap all of that into one statement. If there is a simpler way to write this, would be appreciated if you can help with that. It's the columns that have more than one way of saying pending that is breaking my formula (the two columns with the OR statement).

[#5]

@jl3782 

From formula point of view that could be like

=IF(COUNTIF($S2:$AH2,"Pendiente")=16,
    IF(OR(AI2="Pendiente",AI2="No Asignado"),
        IF(OR(AC2="Pendiente",AI2="No Asignado"),
         "Not Complete",
         "Complete"
        ),
        "Not Complete"
   ),
   "Not Complete"
)

but I don't know what is the actual logic shall be.