Formulas and Functions

30<45add30...","categoryID":322,"dateInserted":"2020-11-30T15:17:15+00:00","dateUpdated":null,"dateLastComment":"2020-11-30T22:16:10+00:00","insertUserID":129196,"insertUser":{"userID":129196,"name":"MeganM","url":"https:\/\/community.smartsheet.com\/profile\/MeganM","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-12-31T14:58:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":129196,"lastUser":{"userID":129196,"name":"MeganM","url":"https:\/\/community.smartsheet.com\/profile\/MeganM","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-12-31T14:58:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":53,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/73541\/if-formula-using-dates-and-adding-to-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73541\/if-formula-using-dates-and-adding-to-date","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"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":73544,"type":"question","name":"COUNTIFS with OR included","excerpt":"Good Afternoon, I've got a doozy of a formula that I just can't figure out. I've been on the phone with smartsheet and it is still not counting correctly. Here is what I need it to count: -Range 1=on or before 12\/1\/20 -Range 2: Consultant name listed in the row -Range 3: Blank OR (on or after 1\/1\/21) Here is the formula I…","categoryID":322,"dateInserted":"2020-11-30T17:18:18+00:00","dateUpdated":null,"dateLastComment":"2020-11-30T21:02:18+00:00","insertUserID":129202,"insertUser":{"userID":129202,"name":"Jenna Matthews","url":"https:\/\/community.smartsheet.com\/profile\/Jenna%20Matthews","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-12-28T18:54:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":129202,"lastUser":{"userID":129202,"name":"Jenna Matthews","url":"https:\/\/community.smartsheet.com\/profile\/Jenna%20Matthews","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-12-28T18:54:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":63,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/73544\/countifs-with-or-included","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73544\/countifs-with-or-included","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-11-30T21:02:27+00:00","dateAnswered":"2020-11-30T17:56:10+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":73549,"type":"question","name":"#Invalid Operation when attempting to find difference between two cells","excerpt":"Good afternoon! I am trying to get the difference between two cells (both from formulas) but get the \"Invalid Operation\" instead. I know this is simple and I'm overlooking something!","categoryID":322,"dateInserted":"2020-11-30T19:32:57+00:00","dateUpdated":null,"dateLastComment":"2020-11-30T19:45:06+00:00","insertUserID":126244,"insertUser":{"userID":126244,"name":"Dawn Jackson","url":"https:\/\/community.smartsheet.com\/profile\/Dawn%20Jackson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zA9X8yGLxnQ!c2lXhzGy_bw!acDcWV_gXXM","dateLastActive":"2020-12-22T17:31:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-08-24T21:03:19+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":85,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/73549\/invalid-operation-when-attempting-to-find-difference-between-two-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73549\/invalid-operation-when-attempting-to-find-difference-between-two-cells","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/D9ZH6NO5I7DO\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"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":73493,"type":"question","name":"How to use AND and OR in an IF function?","excerpt":"So the answer has to be Operator plus one of the other 3 \"OR\" options. IF [Operator or Engineer]@row = \"Operator\" AND either [Shift\/Available]@row = \"1-Grave\" or [Shift\/Available]@row = \"3-Swing\", or [On or Off Site]@row = \"Off site\" then check the box. =IF(AND(OR([Shift\/Available]@row = \"1-Grave\", [Shift\/Available]@row =…","categoryID":322,"dateInserted":"2020-11-25T19:48:36+00:00","dateUpdated":null,"dateLastComment":"2020-11-30T19:35:29+00:00","insertUserID":119921,"insertUser":{"userID":119921,"name":"Marla Berge","url":"https:\/\/community.smartsheet.com\/profile\/Marla%20Berge","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-06-21T18:32:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":119921,"lastUser":{"userID":119921,"name":"Marla Berge","url":"https:\/\/community.smartsheet.com\/profile\/Marla%20Berge","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-06-21T18:32:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":111,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/73493\/how-to-use-and-and-or-in-an-if-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73493\/how-to-use-and-and-or-in-an-if-function","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-11-30T19:34:37+00:00","dateAnswered":"2020-11-25T20:06:52+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":73439,"type":"question","name":"How Do I Return a Negative Number","excerpt":"Hi, I need to return a negative number in a cell. The example is that a Change Order has been submitted, but not yet approved. Therefore, it should not be counted in the Actual Price column. If it is Denied, it would also return a negative number. Thank you.","categoryID":322,"dateInserted":"2020-11-24T15:10:47+00:00","dateUpdated":null,"dateLastComment":"2020-11-30T18:25:24+00:00","insertUserID":129086,"insertUser":{"userID":129086,"name":"Margaret Evans","url":"https:\/\/community.smartsheet.com\/profile\/Margaret%20Evans","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-11-30T19:35:20+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":129086,"lastUser":{"userID":129086,"name":"Margaret Evans","url":"https:\/\/community.smartsheet.com\/profile\/Margaret%20Evans","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-11-30T19:35:20+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":1001,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/73439\/how-do-i-return-a-negative-number","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73439\/how-do-i-return-a-negative-number","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-11-30T19:35:17+00:00","dateAnswered":"2020-11-30T18:09:51+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":73469,"type":"question","name":"Joining multiple multi-select entries from different columns into a single cell WITHOUT DUPLICATES","excerpt":"Hello, as above, I am trying to join the data of all cells within the same row into a single cell without duplicates. In the screenshot below I am working with the following formula: =JOIN(DISTINCT([Column3]2:[Column6]2), CHAR(10)) How can I modify this so that instead of the result being \"A B B C\" the duplicate entries…","categoryID":322,"dateInserted":"2020-11-25T03:52:32+00:00","dateUpdated":null,"dateLastComment":"2020-11-30T18:01:30+00:00","insertUserID":129115,"insertUser":{"userID":129115,"name":"Ryan Stephens","url":"https:\/\/community.smartsheet.com\/profile\/Ryan%20Stephens","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-12-09T02:34:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":127476,"lastUser":{"userID":127476,"name":"Leibel S","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Leibel%20S","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-17T17:47:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":409,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/73469\/joining-multiple-multi-select-entries-from-different-columns-into-a-single-cell-without-duplicates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73469\/joining-multiple-multi-select-entries-from-different-columns-into-a-single-cell-without-duplicates","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/F0A22O9GRJ87\/smartsheet-join.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"SMARTSHEET JOIN.png"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"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