Formulas and Functions

I would just click there on row two and indent it once so that it is a child of row 1.<\/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":107587,"type":"question","name":"Help with formula","excerpt":"I need some help with a formula. See sheet summary. I just need help with the first one \"move in items open\" and I can apply the same formula structure to the rest. I want to count the amount of Warranty phase column if that says \"move in items\" and the status is Not started or In progress\" and have that be 1 number. Any…","snippet":"I need some help with a formula. See sheet summary. I just need help with the first one \"move in items open\" and I can apply the same formula structure to the rest. I want to…","categoryID":322,"dateInserted":"2023-07-13T20:10:58+00:00","dateUpdated":null,"dateLastComment":"2023-07-13T20:32:18+00:00","insertUserID":144391,"insertUser":{"userID":144391,"name":"Jon Barto","url":"https:\/\/community.smartsheet.com\/profile\/Jon%20Barto","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T21:51:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":144391,"lastUser":{"userID":144391,"name":"Jon Barto","url":"https:\/\/community.smartsheet.com\/profile\/Jon%20Barto","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T21:51:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":18,"score":null,"hot":3378560596,"url":"https:\/\/community.smartsheet.com\/discussion\/107587\/help-with-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107587\/help-with-formula","format":"Rich","lastPost":{"discussionID":107587,"commentID":385132,"name":"Re: Help with formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385132#Comment_385132","dateInserted":"2023-07-13T20:32:18+00:00","insertUserID":144391,"insertUser":{"userID":144391,"name":"Jon Barto","url":"https:\/\/community.smartsheet.com\/profile\/Jon%20Barto","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T21:51:45+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\/TQ7LFUFZ13RR\/warranty.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"warranty.jpg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-13T20:47:26+00:00","dateAnswered":"2023-07-13T20:19:40+00:00","acceptedAnswers":[{"commentID":385121,"body":"

Give this a try:<\/p>

=COUNTIFS([Warranty Phase]:[Warranty Phase], \"Move in Items\", [Status]:[Status], OR(@cell = \"In Progress\", @cell = \"Not Started\"))<\/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":107571,"type":"question","name":"Yellow Code Not Working in Formula","excerpt":"I have the underlying code in a sheet to look at specific items in a row. The Red and Green Code is working as expected by my yellow code is not. I am assuming it has to do with the way I have sestup the today function I have tried both =TODAY(2) and >=TODAY(2) niether seems to work. IF(AND([Due Date]@row = TODAY(2),…","snippet":"I have the underlying code in a sheet to look at specific items in a row. The Red and Green Code is working as expected by my yellow code is not. I am assuming it has to do with…","categoryID":322,"dateInserted":"2023-07-13T17:28:22+00:00","dateUpdated":null,"dateLastComment":"2023-07-13T21:58:08+00:00","insertUserID":162256,"insertUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtf4bLRCxTWbiShmV4wW7Ah1awahmzvkEsk_gLoMMj4VnA=s96-c","dateLastActive":"2023-07-13T22:10:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":162256,"lastUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtf4bLRCxTWbiShmV4wW7Ah1awahmzvkEsk_gLoMMj4VnA=s96-c","dateLastActive":"2023-07-13T22:10:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":30,"score":null,"hot":3378558990,"url":"https:\/\/community.smartsheet.com\/discussion\/107571\/yellow-code-not-working-in-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107571\/yellow-code-not-working-in-formula","format":"Rich","lastPost":{"discussionID":107571,"commentID":385170,"name":"Re: Yellow Code Not Working in Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385170#Comment_385170","dateInserted":"2023-07-13T21:58:08+00:00","insertUserID":162256,"insertUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtf4bLRCxTWbiShmV4wW7Ah1awahmzvkEsk_gLoMMj4VnA=s96-c","dateLastActive":"2023-07-13T22:10:03+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-07-13T21:57:50+00:00","dateAnswered":"2023-07-13T20:00:01+00:00","acceptedAnswers":[{"commentID":385104,"body":"

Try this:<\/p>

=IF(Status@row <> \"Complete\", IF([Due Date]@row< TODAY(), \"Red\", IF([Due Date]@row>= TODAY(3), \"Green\", \"Yellow\"))<\/p>

<\/p>

The main issue with your formula is that you are saying to only flag yellow when the due date is equal to TODAY(2) instead of less than or equal to.<\/p>"},{"commentID":385144,"body":"

There are a number of ways to do this. The most secure way would actually mean a restructure and an OR statement:<\/p>

=IF(Status@row <> \"Complete\", IF(OR([Due Date]@row = \"\", [Due Date]@row.= TODAY(3)), \"Green\", IF([Due Date]@row< TODAY(), \"Red\", \"Yellow\"))<\/p>

<\/p>

I moved the Green argument to be first because sometimes Smartsheet will read a blank cell as being less than a non-blank cell. If we had left it in the original order, this would have meant it would flag as true on the red argument when the due date was blank.<\/p>

<\/p>

Of course there are a number of other ways to do it, but this would be my personally preferred method.<\/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":[]}">