IF AND/OR for Text Return
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
Comments
-
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"))
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
-
Excellent!
Happy to help!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!