If formula with several criteria

Kirsteen Leckie
Kirsteen Leckie ✭✭
edited 06/19/23 inFormulas and Functions

Hi,

I was working on a formula last week, but I am still struggling with a second criteria on this.

the way its set up, updating the field depending on dates being input into others. this works well as follows;

=IFERROR(IF([Client review Required]@row = "Yes", IF([Actual Submit Date]@row = "", "Pending", (IF([Actual Compete date]@row = "", "Internal Review", (IF([Actual Date to Client]@row = "", "Send To Client", (IF([Actual Client comments]@row = "", "Client Review", (IF([Actual Piramal Response]@row = "", "Addressing Comments", (IF([Actual Final Client Approval]@row = "", "Client Approval", (IF([Actual submission to QA]@row = "", "QA Review", (IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved"))))))))))))))), ""), "")

However this is only if the Client review required cell is 'Yes' I am trying to resolve when the cell is 'No' - where 'No' is selected the status must miss the middle Status sections and jump right to 'QA Review and then 'Approved' As per this latter section - "(IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved"))"

I am really struggling on how I get this section into the above formula - or if this is even possible?

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭
    Answer ✓

    @Kirsteen Leckie

    I made some tweaks to the formula, please try below

    =IF([Client review Required]@row = "Yes", IF([Actual Submit Date]@row = "", "Pending", (IF([Actual Compete date]@row = "", "Internal Review", (IF([Actual Date to Client]@row = "", "Send To Client", (IF([Actual Client comments]@row = "", "Client Review", (IF([Actual Piramal Response]@row = "", "Addressing Comments", (IF([Actual Final Client Approval]@row = "", "Client Approval", (IF([Actual submission to QA]@row = "", "QA Review", (IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved"))))))))))))))), IF([Client review Required]@row = "No", IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved")))

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE,Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/


    Did this answer help you? Show some love by marking this answer as "Insightful" or "Awesome❤️" and "Vote Up⬆️"

    · Share on Facebook Share on Twitter

Answers

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭

    Hi@Kirsteen Leckie

    You may want to try below variation

    =IFERROR(IF([Client review Required]@row = "Yes", IF([Actual Submit Date]@row = "", "Pending", (IF([Actual Compete date]@row = "", "Internal Review", (IF([Actual Date to Client]@row = "", "Send To Client", (IF([Actual Client comments]@row = "", "Client Review", (IF([Actual Piramal Response]@row = "", "Addressing Comments", (IF([Actual Final Client Approval]@row = "", "Client Approval", (IF([Actual submission to QA]@row = "", "QA Review", (IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved"))))))))))))))), "",IF([Client review Required]@row = "No",IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved"))),"")

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE,Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/


    Did this answer help you? Show some love by marking this answer as "Insightful" or "Awesome❤️" and "Vote Up⬆️"

    · Share on Facebook Share on Twitter
  • Kirsteen Leckie
    Kirsteen Leckie ✭✭

    Hi@Amit Wadhwani,

    Thank you for your help, however this is generating a #INCORRECT ARGUMENT SET error. apologies, I am really struggling with getting my head around this one!

    · Share on Facebook Share on Twitter
  • Hollie Green
    Hollie Green ✭✭✭✭✭
    edited 06/19/23

    If the Client review required will always be filled in with either a yes or a no you can put it where the false value would go for the Client review required = "yes" would be in your formula.

    When I have an extremely long formula like that I usually click after each , to find where it tells me my first value if false goes.

    · Share on Facebook Share on Twitter
  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭
    Answer ✓

    @Kirsteen Leckie

    I made some tweaks to the formula, please try below

    =IF([Client review Required]@row = "Yes", IF([Actual Submit Date]@row = "", "Pending", (IF([Actual Compete date]@row = "", "Internal Review", (IF([Actual Date to Client]@row = "", "Send To Client", (IF([Actual Client comments]@row = "", "Client Review", (IF([Actual Piramal Response]@row = "", "Addressing Comments", (IF([Actual Final Client Approval]@row = "", "Client Approval", (IF([Actual submission to QA]@row = "", "QA Review", (IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved"))))))))))))))), IF([Client review Required]@row = "No", IF([Actual Final or QA Approval]@row = "", "QA Review", "Approved")))

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE,Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/


    Did this answer help you? Show some love by marking this answer as "Insightful" or "Awesome❤️" and "Vote Up⬆️"

    · Share on Facebook Share on Twitter
  • Kirsteen Leckie
    Kirsteen Leckie ✭✭

    @Amit WadhwaniThank you!

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Here's another option:<\/p>

In your sheet, create a third section that adds the results of the first two. It would have the same rows, but the formula for each row would add the cells of section 1 + section 2.<\/p>

Add a helper column called ShowInReport as a Check Box and check all the rows in the third section. <\/p>

Create a report and tell it to show you only Section 3 (where the ShowInReport is true.<\/p>

I hope that makes sense.<\/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":108864,"type":"question","name":"Is there a formula to show as %","excerpt":"What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a decimal instead of a %. Below is the formula I am currently using. Is there a way to make it show as % =\"We are at \" + [% closed rate]@row + \"…","snippet":"What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a…","categoryID":322,"dateInserted":"2023-08-11T17:22:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T19:29:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":144360,"lastUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":36,"score":null,"hot":3383558496,"url":"https:\/\/community.smartsheet.com\/discussion\/108864\/is-there-a-formula-to-show-as","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108864\/is-there-a-formula-to-show-as","format":"Rich","lastPost":{"discussionID":108864,"commentID":390301,"name":"Re: Is there a formula to show as %","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390301#Comment_390301","dateInserted":"2023-08-11T19:29:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+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-11T19:29:56+00:00","dateAnswered":"2023-08-11T17:49:58+00:00","acceptedAnswers":[{"commentID":390269,"body":"

Try this - =\"We are at \" + [% closed rate]@row * 100 + \"% closed rate on ticket status for the month of \"+[Month]@row.<\/p>"},{"commentID":390301,"body":"

I figured it out! Updated formula to get the 2 decimal places as well.<\/p>

=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/comment/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/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":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":59,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+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-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"

You were, indeed, very close.<\/p>

=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula\n <\/a>\n<\/div>\n

It looks like you forgot to close out the AND <\/strong>funtion. Try this<\/p>

=IF([Invoice Received?]@row = 0, \"Green\", IF(AND([Renewal Date]@row >= TODAY(0), [Renewal Date]@row <= TODAY(+30)), \"Red\", \"Yellow\"))<\/code><\/p>

Hope this helps!<\/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

  • Join formula of two sheets

    Hello - Shown is one sheet but two sources separate data . Can I combine the two counts anyway they have the same Primary column. I don't want to make a summary count of the two because I can't get rid of this in reporting. Can you assist me with the formula? Thanks.

  • Is there a formula to show as %

    What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a decimal instead of a %. Below is the formula I am currently using. Is there a way to make it show as % ="We are at " + [% closed rate]@row + "…

  • IF/AND Formula

    Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from "unparsable" to "incorrect…