Formulas and Functions

= 14, \"Yes\", IF(14 > [Gap vs SMS]15 >= 0, \"Hold\", IF([Gap vs SMS]15 < 0, \"No\"))) Formula works ok, but when I copy to other sheets, a few of them appear with invalid operation and I can´t find what could be…","categoryID":322,"dateInserted":"2021-04-16T21:27:07+00:00","dateUpdated":null,"dateLastComment":"2021-04-19T15:23:40+00:00","insertUserID":134404,"insertUser":{"userID":134404,"name":"Rosario Barros","url":"https:\/\/community.smartsheet.com\/profile\/Rosario%20Barros","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-04-19T22:49:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":134404,"lastUser":{"userID":134404,"name":"Rosario Barros","url":"https:\/\/community.smartsheet.com\/profile\/Rosario%20Barros","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-04-19T22:49:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":71,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/78474\/if-invalid-operation","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/78474\/if-invalid-operation","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-04-17T09:22:15+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":78488,"type":"question","name":"How can I identify multi-select cell values that are not in the dropdown list?","excerpt":"I have a multi select column \"Cost Code.\" There are currently values in the column that are not within the dropdown list. For the \"Accessory Code\" I was able to check and flag these via the following formula where I reference a column in a separate =IF(CONTAINS([Accessory Type]@row, {A&A Column Selections Range 1}), 0, 1)…","categoryID":322,"dateInserted":"2021-04-18T18:15:26+00:00","dateUpdated":null,"dateLastComment":"2021-04-19T14:57:39+00:00","insertUserID":132080,"insertUser":{"userID":132080,"name":"Bailey Neal","url":"https:\/\/community.smartsheet.com\/profile\/Bailey%20Neal","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJw1IqX5yq_C3WlMcH3-VSnlcmY4ikeC5sDxBwTY=s96-c","dateLastActive":"2021-12-12T00:53:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":132080,"lastUser":{"userID":132080,"name":"Bailey Neal","url":"https:\/\/community.smartsheet.com\/profile\/Bailey%20Neal","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJw1IqX5yq_C3WlMcH3-VSnlcmY4ikeC5sDxBwTY=s96-c","dateLastActive":"2021-12-12T00:53:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":376,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/78488\/how-can-i-identify-multi-select-cell-values-that-are-not-in-the-dropdown-list","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/78488\/how-can-i-identify-multi-select-cell-values-that-are-not-in-the-dropdown-list","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/I9V2KUBXFI1D\/scrnli-4-18-2021-1-02-51-pm.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"scrnli_4_18_2021_1-02-51 PM.png"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-04-19T14:57:39+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}],"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}]},{"discussionID":78508,"type":"question","name":"two columns to power a red flag in another column","excerpt":"If I have an urgency status that is gray (indicating a deal that is either dead, closed, or on pause), coupled with a priority status listed as medium or high, I would like to power an additional column titled \"Discrepancy\" to show up with a red flag. Thank you!","categoryID":322,"dateInserted":"2021-04-19T13:40:15+00:00","dateUpdated":null,"dateLastComment":"2021-04-19T14:35:04+00:00","insertUserID":126604,"insertUser":{"userID":126604,"name":"Shannon Scarbrough","url":"https:\/\/community.smartsheet.com\/profile\/Shannon%20Scarbrough","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-12-22T17:58:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":126604,"lastUser":{"userID":126604,"name":"Shannon Scarbrough","url":"https:\/\/community.smartsheet.com\/profile\/Shannon%20Scarbrough","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-12-22T17:58:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":62,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/78508\/two-columns-to-power-a-red-flag-in-another-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/78508\/two-columns-to-power-a-red-flag-in-another-column","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-04-19T14:34:54+00:00","dateAnswered":"2021-04-19T13:54:48+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":78509,"type":"question","name":"SUMIFS based on another cell","excerpt":"I want to add cells (SUM) if there is text in another cell. So, basically I need to add the sum form the Total Issue column if there is any text in the Region column. Here is the example: Columns: Total Issues Region Here is the formula: =SUMIFS([Total Issues]:[Total Issues]) AND( [Region]:[Region], ISTEXT(@cell)) Thank…","categoryID":322,"dateInserted":"2021-04-19T14:19:19+00:00","dateUpdated":null,"dateLastComment":"2021-04-19T14:26:17+00:00","insertUserID":128829,"insertUser":{"userID":128829,"name":"Justin Mauzy","url":"https:\/\/community.smartsheet.com\/profile\/Justin%20Mauzy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-26T16:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":128829,"lastUser":{"userID":128829,"name":"Justin Mauzy","url":"https:\/\/community.smartsheet.com\/profile\/Justin%20Mauzy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-26T16:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":89,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/78509\/sumifs-based-on-another-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/78509\/sumifs-based-on-another-cell","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-04-19T14:26:27+00:00","dateAnswered":"2021-04-19T14:26:17+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":78461,"type":"question","name":"Traffic light formula problem with two conditions","excerpt":"Dear I have a column of% Complete (% Complete) and a Task Completion (ENDDATE) I would like to generate the following conditions: 1. Green if 100% of the task is complete and it has been given on the estimated date (which is equal to or less than today) 2. Yellow if it is between 51% and 99% complete as of today 3. Red if…","categoryID":322,"dateInserted":"2021-04-16T17:27:04+00:00","dateUpdated":null,"dateLastComment":"2021-04-19T14:20:39+00:00","insertUserID":134022,"insertUser":{"userID":134022,"name":"Alfredo Alva","url":"https:\/\/community.smartsheet.com\/profile\/Alfredo%20Alva","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-06-18T21:37:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-25T20:36:29+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":252,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/78461\/traffic-light-formula-problem-with-two-conditions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/78461\/traffic-light-formula-problem-with-two-conditions","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-04-19T14:20:39+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Help Article Resources