Formulas and Functions

=COUNTIF([Level III Request]:[Level III Request], CONTAINS(\"Solution Video\", @cell))<\/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":111635,"type":"question","name":"Is there a better way???","excerpt":"=IF($[Year Select]$2 = \"2023\", [2023]@row, \"//www.santa-greenland.com/community/categories/formulas-and-functions/\" + IF($[Year Select]$2 = \"2022\", [2022]@row, \"//www.santa-greenland.com/community/categories/formulas-and-functions/\")) I am having an issue where I can select 2023 just fine, but when I select 2022 all of my reference dates change to a \"Text date\" and the formula in the data 1 & 2 columns do not recognize it. Is there an easy way to fix this? Or,…","snippet":"=IF($[Year Select]$2 = \"2023\", [2023]@row, \"//www.santa-greenland.com/community/categories/formulas-and-functions/\" + IF($[Year Select]$2 = \"2022\", [2022]@row, \"//www.santa-greenland.com/community/categories/formulas-and-functions/\")) I am having an issue where I can select 2023 just fine, but when I select 2022 all…","categoryID":322,"dateInserted":"2023-10-13T14:05:38+00:00","dateUpdated":null,"dateLastComment":"2023-10-13T18:20:55+00:00","insertUserID":168460,"insertUser":{"userID":168460,"name":"Mark.Hendley","url":"https:\/\/community.smartsheet.com\/profile\/Mark.Hendley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-13T20:38:25+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168460,"lastUser":{"userID":168460,"name":"Mark.Hendley","url":"https:\/\/community.smartsheet.com\/profile\/Mark.Hendley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-13T20:38:25+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":37,"score":null,"hot":3394430793,"url":"https:\/\/community.smartsheet.com\/discussion\/111635\/is-there-a-better-way","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111635\/is-there-a-better-way","format":"Rich","tagIDs":[219,254,319],"lastPost":{"discussionID":111635,"commentID":399944,"name":"Re: Is there a better way???","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/399944#Comment_399944","dateInserted":"2023-10-13T18:20:55+00:00","insertUserID":168460,"insertUser":{"userID":168460,"name":"Mark.Hendley","url":"https:\/\/community.smartsheet.com\/profile\/Mark.Hendley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-13T20:38:25+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\/11XRBBCD2KCH\/capture1-png.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/11XRBBCD2KCH\/capture1-png.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/11XRBBCD2KCH\/capture1-png.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/11XRBBCD2KCH\/capture1-png.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/11XRBBCD2KCH\/capture1-png.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/11XRBBCD2KCH\/capture1-png.png"},"alt":"Capture1.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-13T18:21:18+00:00","dateAnswered":"2023-10-13T18:20:55+00:00","acceptedAnswers":[{"commentID":399944,"body":"

@Andrée Starå<\/a> I have found a solution, it was from a post in 2019 that had a similar but different problem<\/p>

Instead of adding(+) formulas together, you should nest them.<\/p>

Comment from @Paul Newcome<\/a> below<\/p>

To nest IF statements, you would basically drop the next one into the previous one's \"else\" section then close out all of the IF's at the end. Eventually it would look something like this...<\/p>

=IF(this is true, do this, IF(that is true, do that, IF(something is true, do something)))<\/p>

<\/p>

For me that looks like this...<\/p>

=IF($[Year Select]$2 = 2023, [2023]@row, IF($[Year Select]$2 = 2022, [2022]@row, IF($[Year Select]$2 = 2021, [2021]@row, IF($[Year Select]$2 = 2020, [2020]@row, IF($[Year Select]$2 = 2019, [2019]@row)))))<\/p>

<\/p>

Hope this helps others!<\/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":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":319,"urlcode":"functionality","name":"functionality"}]},{"discussionID":111629,"type":"question","name":"IF\/And Formula to Change Symbols","excerpt":"I've been looking at this for a while and can't figure out what is going on -- turning to my Smartsheet community to point out what is likely very obvious! I'm trying to get the status symbol to change as follows: If =>100% green, if =>80% yellow, and if less than 80% Red. I'm having the trouble with the Yellow Here's the…","snippet":"I've been looking at this for a while and can't figure out what is going on -- turning to my Smartsheet community to point out what is likely very obvious! I'm trying to get the…","categoryID":322,"dateInserted":"2023-10-13T11:31:14+00:00","dateUpdated":null,"dateLastComment":"2023-10-13T15:15:30+00:00","insertUserID":159705,"insertUser":{"userID":159705,"name":"TolerDo","url":"https:\/\/community.smartsheet.com\/profile\/TolerDo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/K845ZBZXY4LT\/nCR0RL2FFUR5C.jpg","dateLastActive":"2023-10-13T15:33:24+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":112046,"lastUser":{"userID":112046,"name":"Razetto","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Razetto","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-13T19:05:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":27,"score":null,"hot":3394407404,"url":"https:\/\/community.smartsheet.com\/discussion\/111629\/if-and-formula-to-change-symbols","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111629\/if-and-formula-to-change-symbols","format":"Rich","lastPost":{"discussionID":111629,"commentID":399873,"name":"Re: IF\/And Formula to Change Symbols","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/399873#Comment_399873","dateInserted":"2023-10-13T15:15:30+00:00","insertUserID":112046,"insertUser":{"userID":112046,"name":"Razetto","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Razetto","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-13T19:05:46+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\/2AN8HWHYZ0ZI\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/2AN8HWHYZ0ZI\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/2AN8HWHYZ0ZI\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/2AN8HWHYZ0ZI\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/2AN8HWHYZ0ZI\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/2AN8HWHYZ0ZI\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-13T15:35:07+00:00","dateAnswered":"2023-10-13T15:15:30+00:00","acceptedAnswers":[{"commentID":399873,"body":"

@TolerDo<\/a> Hi, try something like this instead =IF(NOT(ISBLANK([Column4]@row)), IF(AND([Column4]@row >= 80, [Column4]@row < 100), \"Yellow\", IF([Column4]@row = 100, \"Green\", \"Red\"))) The isn't blank part is because it shows as red when the cell is blank, but it can removed.<\/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":[]}">