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
-
Nic Larsen ✭✭✭✭✭✭
-
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,[email protected])
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:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
Help Article Resources
Categories
=IF(OR([Added to CRM]@row = \"No\", [Added to CRM]@row = \"//www.santa-greenland.com/community/discussion/46546/\"), \"Open\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108402,"type":"question","name":"IF CONTAINS Help!","excerpt":"Good morning, I want to return specific values in a cell based on a drop-down list (single values per column), for example, if Activity = Marketing, then return value 45. However I need multiple options in each column, so I need the formula to then look for another activity and return a different value based on that.…","snippet":"Good morning, I want to return specific values in a cell based on a drop-down list (single values per column), for example, if Activity = Marketing, then return value 45. However…","categoryID":322,"dateInserted":"2023-08-02T09:54:15+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T12:54:03+00:00","insertUserID":148086,"insertUser":{"userID":148086,"name":"Lisa B 2022","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20B%202022","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxbls3ea1kMuE-s37Yj-hmWMgVFXC1jOGRq6oYZd=s96-c","dateLastActive":"2023-08-02T14:41:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":148086,"lastUser":{"userID":148086,"name":"Lisa B 2022","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20B%202022","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxbls3ea1kMuE-s37Yj-hmWMgVFXC1jOGRq6oYZd=s96-c","dateLastActive":"2023-08-02T14:41:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":23,"score":null,"hot":3381952098,"url":"https:\/\/community.smartsheet.com\/discussion\/108402\/if-contains-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108402\/if-contains-help","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108402,"commentID":388450,"name":"Re: IF CONTAINS Help!","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388450#Comment_388450","dateInserted":"2023-08-02T12:54:03+00:00","insertUserID":148086,"insertUser":{"userID":148086,"name":"Lisa B 2022","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20B%202022","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxbls3ea1kMuE-s37Yj-hmWMgVFXC1jOGRq6oYZd=s96-c","dateLastActive":"2023-08-02T14:41:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-02T12:53:23+00:00","dateAnswered":"2023-08-02T10:53:12+00:00","acceptedAnswers":[{"commentID":388395,"body":"