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

Tags:

Comments

  • Nic Larsen
    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"))

  • 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å
    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å
    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å
    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

想实践工作直接与公式Smartsheet?

Check out the公式手册模板!
Try this:<\/p>

=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":"

Hi Lisa B 2022,<\/strong><\/a><\/p>

You can try this formula for your condition-<\/p>

=IF(CONTAINS(\"Advertising\", Activity@row), 45, IF(CONTAINS(\"Operations\", Activity@row), 30, \"//www.santa-greenland.com/community/discussion/46546/\"))<\/p>

\n
\n \n \"MicrosoftTeams-image<\/img><\/a>\n <\/div>\n<\/div>\n


<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108396,"type":"question","name":"=COUNT({Approval Status}) prevent reporting lines","excerpt":"Hi All I am using on of the templates cost tracking. On one of the intake submitted sheets it has 2 lines saying <> This is fine although it is showing up on the cost metrics as 2 submitted jobs, is there a way to prevent this or do i delete the lines that say <>?","snippet":"Hi All I am using on of the templates cost tracking. On one of the intake submitted sheets it has 2 lines saying <> This is fine although it is showing up on the…","categoryID":322,"dateInserted":"2023-08-02T06:35:45+00:00","dateUpdated":"2023-08-02T11:36:08+00:00","dateLastComment":"2023-08-02T13:22:31+00:00","insertUserID":135720,"insertUser":{"userID":135720,"name":"Craig Beattie","url":"https:\/\/community.smartsheet.com\/profile\/Craig%20Beattie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T14:33:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":91566,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-02T18:59:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3381942496,"url":"https:\/\/community.smartsheet.com\/discussion\/108396\/count-approval-status-prevent-reporting-lines","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108396\/count-approval-status-prevent-reporting-lines","format":"Rich","lastPost":{"discussionID":108396,"commentID":388465,"name":"Re: =COUNT({Approval Status}) prevent reporting lines","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388465#Comment_388465","dateInserted":"2023-08-02T13:22:31+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-02T18:59: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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/4SDPWL5WY5R0\/screenshot1-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"screenshot1.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-02T12:59:35+00:00","dateAnswered":"2023-08-02T12:52:48+00:00","acceptedAnswers":[{"commentID":388448,"body":"

Are there any (possibly hidden) columns that have formulas in them? If the template is pre-column formula, you used to have to keep two rows in the template so that the built in auto-fill feature would grab the formulas as new rows were added.<\/p>


<\/p>

If there are formulas, converting them to column formulas should allow you to delete those two rows.<\/p>


<\/p>

If you would rather leave them in, you can change from a COUNT to a COUNTIFS and exclude any rows that are \"<<Do Not Delete>>\" like so:<\/p>

=COUNTIFS({Range}, @cell <> \"Do Not Delete\")<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions