Is it possible to create two IF's statements in a single cell in Excel?-CodePudding

I'm trying to make cell G3 say "Completed" when all cells from A3:E3 say "Completed" (for this, Im using =IF(AND) statement). Also trying to make G3 say "In progress" when either from A3:E3 say "Started" (for this I'm using =OR statement).

I'm very new into this, sorry about my ignorance.

=IF(AND(A3="Completed",C3="Completed",E3="Completed"),"Completed","Pending")=IF(OR(A3:E3="Started"),"In Progress")

Cell G3 with a FALSE value, not sure how to put together this formula

CodePudding user response：

There's two ways to do this. One is the nested IF() approach, where you embed or "nest" IF() statements into other IF() statements. You haven't done that correctly in your example, and there's multiple problems.

The only acceptable format for IF() is:

=IF( condition , value if true , value if false)

And thus your nested IF() would embed another full IF() statement in place of value if false, not simply adding it to the end with another = sign:

=IF(AND(A3="Completed",C3="Completed",E3="Completed"),"Completed", IF(
OR(A3:E3), "Started", "Pending" )

The second IF() is put in the place where "result if the first IF() is false" would go.

But Excel provides a way to do a sequence of IF() conditions that allows the "ELSEIF" construct in many programming languages, and allows logic similar to the way a "CASE" statement works in other languages. and that is IFS(), plural, with an 's'.

=IFS( condition1, result if true, condition2, result if true, condition3, result if true)

What you end up with is:

=IFS( AND(A3="Completed",C3="Completed",E3="Completed") , "Completed" ,
OR(A3:E3) , "Started")

This tests in order. If condition1 is true, return "Completed" and be done. If not, move on to condition2, and if that's true, return "Started" and be done.

You want one more condition though. If 1 or 2 isn't true, then make the result "Pending." How do we do that, since IFS() requires PAIRED conditions and results? Since they're checked in order and the IFS() only continues to the next condition if there hasn't been a match yet, we make a third condition of simply TRUE:

=IFS( AND(A3="Completed",C3="Completed",E3="Completed") , "Completed" ,
OR(A3:E3) , "Started", TRUE, "Pending" )

The third test is not required by the IFS() statement, but most of the time we still want an "otherwise, return x".

CodePudding user response：

For the sake of readability you could also use the new LET-formula (if you are on Excel 365:

=LET(cntCompleted,COUNTIFS(A8:E8,"completed"),
     cntStarted,COUNTIFS(A8:E8,"started"),
     IF(cntCompleted=3,"completed",
     IF(cntStarted>=1,"in progess",
     "pending")))

The formula counts "completed" and "started" - then uses the IF-statement to check the conditions.

CodePudding user response：

Just a thought, you could count the number of "completed" in the range and test to see if the result is equal to 5:

=countif(A3:E3,"completed")=5

Which will return True or False, then you can continue other calculations...