Formulas and Functions

0.7, AND,[% Complete]@row < 1,\"Yellow\"),IF[% Complete]@row = 1,\"Green))) Thank you","categoryID":322,"dateInserted":"2020-09-25T15:57:23+00:00","dateUpdated":null,"dateLastComment":"2020-09-28T12:10:34+00:00","insertUserID":126663,"insertUser":{"userID":126663,"name":"Larissa Avtenieva","url":"https:\/\/community.smartsheet.com\/profile\/Larissa%20Avtenieva","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5EDROWinaWQ!mTfdfv7_ML4!pDOwK927MWb","dateLastActive":"2021-03-01T17:35:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":126663,"lastUser":{"userID":126663,"name":"Larissa Avtenieva","url":"https:\/\/community.smartsheet.com\/profile\/Larissa%20Avtenieva","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5EDROWinaWQ!mTfdfv7_ML4!pDOwK927MWb","dateLastActive":"2021-03-01T17:35:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":127,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/71599\/nested-if-formatting","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/71599\/nested-if-formatting","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-09-28T12:20:57+00:00","dateAnswered":"2020-09-25T20:55:55+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":71394,"type":"question","name":"Struggling with SUMIFS","excerpt":"Dear Community, I am desperately trying to make a SUMIFS function work. I have to columns with dates, Start date (startdato) and end date (slutdato). I need to count the days in the period between start date and end date, and add them, where the following criterias are met: It must say \"Ida Skou Andersen\" in the column…","categoryID":322,"dateInserted":"2020-09-18T12:36:09+00:00","dateUpdated":null,"dateLastComment":"2020-09-28T08:38:51+00:00","insertUserID":126347,"insertUser":{"userID":126347,"name":"Ida Skou Andersen","url":"https:\/\/community.smartsheet.com\/profile\/Ida%20Skou%20Andersen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-11-10T15:29:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T09:29:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":53,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/71394\/struggling-with-sumifs","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/71394\/struggling-with-sumifs","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":71635,"type":"question","name":"How i can add a other currency like THB (Thai Baht), Currencys which are not listed?","excerpt":"Hello Community, I would like to add other currency, THB Thai Baht, Currencys which are not listed? Thx in advance S.Carlo","categoryID":322,"dateInserted":"2020-09-28T07:15:00+00:00","dateUpdated":null,"dateLastComment":"2020-09-28T07:45:52+00:00","insertUserID":123823,"insertUser":{"userID":123823,"name":"Sandro Carlo","url":"https:\/\/community.smartsheet.com\/profile\/Sandro%20Carlo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-08-23T05:55:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T09:29:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":75,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/71635\/how-i-can-add-a-other-currency-like-thb-thai-baht-currencys-which-are-not-listed","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/71635\/how-i-can-add-a-other-currency-like-thb-thai-baht-currencys-which-are-not-listed","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-09-28T07:39:34+00:00","dateAnswered":"2020-09-28T07:22:29+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":71607,"type":"question","name":"Join Function Not Working As Expected","excerpt":"When I join across cells it is taking the last option from the Core Column and the first option in the Calyx Column and combining the text. Any suggestions will be helpful. The formula I am using is =JOIN(Core1:Calyx1, \" \")","categoryID":322,"dateInserted":"2020-09-25T18:10:15+00:00","dateUpdated":null,"dateLastComment":"2020-09-28T07:12:05+00:00","insertUserID":125792,"insertUser":{"userID":125792,"name":"Thomas Bostwick","url":"https:\/\/community.smartsheet.com\/profile\/Thomas%20Bostwick","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T17:59:18+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-06-26T09:32:24+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":119,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/71607\/join-function-not-working-as-expected","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/71607\/join-function-not-working-as-expected","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/40JUL75AEYFO\/2020-09-25-13-56-23.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"2020-09-25_13-56-23.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":71590,"type":"question","name":"Can IF, Contains and VLOOKUP be combined?","excerpt":"Hi all, I am trying to combine IF and CONTAINS with VLOOKUP (refer following) but it is throwing #INCORRECT ARGUMENT =IF(CONTAINS(\"1\", {Count}, VLOOKUP(\"TD\", {Mastersheetrng}, 2))) Can these be combined, or some other combination will work, please help. Prajna ","categoryID":322,"dateInserted":"2020-09-25T09:42:24+00:00","dateUpdated":null,"dateLastComment":"2020-09-28T06:38:45+00:00","insertUserID":122925,"insertUser":{"userID":122925,"name":"Mindfull","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mindfull","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-02-02T11:26:13+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-06-26T09:32:24+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":489,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/71590\/can-if-contains-and-vlookup-be-combined","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/71590\/can-if-contains-and-vlookup-be-combined","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":71625,"type":"question","name":"A Single Formula to SUM a variable range of cells in a row","excerpt":"Hi, I am trying to write a formula that will SUM between one and twenty columns in a row, based on the number in another cell in the same row. If the number is 20, I want to sum all 20 cells in the row. If the number is 19, I want to sum the first 19 cells. The range is always contiguous, and the cells to be summed always…","categoryID":322,"dateInserted":"2020-09-26T18:22:05+00:00","dateUpdated":null,"dateLastComment":"2020-09-28T06:36:32+00:00","insertUserID":122982,"insertUser":{"userID":122982,"name":"Douglas Jerum","url":"https:\/\/community.smartsheet.com\/profile\/Douglas%20Jerum","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xKlQ8aS_7Do!bCDXmy9BSGY!U4F0O8b0uYd","dateLastActive":"2021-12-28T18:23:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T09:29:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":831,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/71625\/a-single-formula-to-sum-a-variable-range-of-cells-in-a-row","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/71625\/a-single-formula-to-sum-a-variable-range-of-cells-in-a-row","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-09-27T23:04:41+00:00","dateAnswered":"2020-09-27T09:41:00+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":71616,"type":"question","name":"How to capture a value from multi-select column","excerpt":"Which formula works better to find out if a value exists in a multi select dropdown column? I have responses collected from a survey and one of the column was multi-select dropdown list type column. I'm trying to separate out the selection values and adding them in a separate sheet (so using cross sheet reference). Have…","categoryID":322,"dateInserted":"2020-09-25T21:30:55+00:00","dateUpdated":null,"dateLastComment":"2020-09-27T22:50:27+00:00","insertUserID":106236,"insertUser":{"userID":106236,"name":"Ameya Athalye","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ameya%20Athalye","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ER8RZ3PE1IR1\/nRBMUH66CUIHM.png","dateLastActive":"2023-05-31T21:29:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":106236,"lastUser":{"userID":106236,"name":"Ameya Athalye","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ameya%20Athalye","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ER8RZ3PE1IR1\/nRBMUH66CUIHM.png","dateLastActive":"2023-05-31T21:29:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":1600,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/71616\/how-to-capture-a-value-from-multi-select-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/71616\/how-to-capture-a-value-from-multi-select-column","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-09-27T22:50:35+00:00","dateAnswered":"2020-09-27T07:21:04+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":219,"urlcode":"Sheets","name":"Sheets"},{"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Help Article Resources