If and ISText formula

[Archie077]

hello everyone

Good day.

test 

I'm currently stuck in my formula, And i need to incorporate logics #4 to #6 to this formula set.

=IF(ISBLANK(A3),"new",IF(A3="invalid","closed",IF(AND(ISTEXT(B3),A3="Valid"),"In-Progress"

logics:

1. if a2 is blank value is " new"
2. if a2=invalid, value is "closed"
3. if a2=valid + b2 (istext) = "in progress"
4. if a2=valid+b2(istext)+c2(istext)="assigned"
5. if a2=valid+b2(istext)+c2(istext)+d3(istext) = "reopen"
6. if a2=valid+b2(istext)+c2(istext)+d3(istext)+e4(istext) = "closed"

thank you for your assistance

[#2]

@Archie077 You need to build your formula, starting with the last criteria. If "valid" and all other cells contain text, then "Closed" and so on. If you don't do that, once the third condition is met it will always  returns "in progress" and never evaluate the last three IF statements. You may also consider using IFS. That would then look something like this:

=IFS(logic6,"closed",logic5,"reopen",logic4,"assigned",logic3,"in progress",logic2,"closed",logic1,"new")

Not convinced though that this is the most effective way to achieve your ultimate goal. Would need to see your actual schedule to recommend possible alternatives.

[#3]

@Archie077 

Perhaps

=IF( ISBLANK(A2), " new",
  IF(A2="invalid", "closed",
  IF( (A2="valid"),
  IF( ISTEXT(B2),
  IF( ISTEXT(C2),
  IF( ISTEXT(D3),
  IF( ISTEXT(E4),
    "closed",
    "reopen"),
    "assigned"),
    "in progress")
))))

[#4]

@Riny_van_Eekelen thank you, i was able to get it