IF AND/OR for Text Return

kimberleeadam

Hello,

I have a tracking sheet that has three phases and each phase has a status, either "Not Started", "In Progress", or "Completed". I have an overall status column so I want to create a formula for the overall status that will return the value of "Not Started", if all phases are not started, "In Progress" if any of the phases are in progress, and "Completed" if the final phase is completed.

I'm not sure what the formula would be for this or if I would be better off using symbols.

=IF(AND([1 Rough Draft Writing Status]7, "Not Started", [2 Rough Draft Review Status]7, "Not Started", [4 PDF Review Status]7, "Not Started"), "Not Started", "In Progress")

Thank you,

Kim

Nic Larsen

Try this: 

=IF([4 PDF Review Status]@row = "Completed", "Completed", IF(OR([1 Rough Draft Writing Status]@row = "In Progress", [2 Rough Draft Writing Status]@row = "In Progress", [4 PDF Review Status]@row = "In Progress"), "In Progress", "Not Started"))

kimberleeadam

I'm still getting a #UNPARSEABLE error

=IF([4 PDF Review Status]7 = "Completed", "Completed", IF(OR([1 Rough Draft Writing Status]7 = "In Progress", [2 Rough Draft Writing Status]7 = "In Progress", [4 PDF Review Status]7 = "In Progress"), "In Progress", "Not Started"))

Andrée Starå

Hi Kim,

Can you maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

kimberleeadam

Yes, I wouldn't mind sharing at all. I don't have any sensitive information in it so no problem. Thank you so much! 

FYI... I was also trying to play around with colored dots so in row 7, you'll see that... row 8 still has the text. To be honest, I kind of like the dots but whatever works would be very cool.

Thank you again!

Andrée Starå

Happy to help!

Try something like this.

Let's start with this and then we can update it to show RYGG later.

I've added so the formula will show In Progress if everything isn't Completed but at least one is.

=IF([4 PDF Review Status]@row = "Completed"; "Completed"; IF(OR(COUNTIF([1 Rough Draft Writing Status]@row:[4 PDF Review Status]@row; "In Progress") > 0; COUNTIF([1 Rough Draft Writing Status]@row:[4 PDF Review Status]@row; "Completed") > 0); "In Progress"; IF(COUNTIF([1 Rough Draft Writing Status]@row:[4 PDF Review Status]@row; "Not Started") = 3; "Not Started")))

The same version but with the below changes for your and others convenience.

=IF([4 PDF Review Status]@row = "Completed", "Completed", IF(OR(COUNTIF([1 Rough Draft Writing Status]@row:[4 PDF Review Status]@row, "In Progress") > 0, COUNTIF([1 Rough Draft Writing Status]@row:[4 PDF Review Status]@row, "Completed") > 0), "In Progress", IF(COUNTIF([1 Rough Draft Writing Status]@row:[4 PDF Review Status]@row, "Not Started") = 3, "Not Started")))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Best,

Andrée

kimberleeadam

This is great! And works awesome! I was able to replace the text with the RYGG and it worked just as well. Thank you so much for your help!

Andrée Starå

Excellent!

Happy to help!

Best,

Andrée