Formulas and Functions

TODAY(), \"Green\", IF([Plan Start]@row < TODAY(), \"Yellow\", IF([Due Date]@row >[Due Date]@row TODAY(), \"Red\") OR(IF([% Complete]@row = \"1\", \"Blue\"))) Essentially I'm trying to make a formula that says: If It's…","categoryID":322,"dateInserted":"2021-03-16T17:07:21+00:00","dateUpdated":null,"dateLastComment":"2021-03-16T22:36:25+00:00","insertUserID":133093,"insertUser":{"userID":133093,"name":"Daniel Crippen","url":"https:\/\/community.smartsheet.com\/profile\/Daniel%20Crippen","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BHPRgbz2Ij4!A5dRx1DNA7w!wYtbkGuvcHG","dateLastActive":"2021-05-05T20:54:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":133131,"lastUser":{"userID":133131,"name":"Sam M.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20M.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-05-27T16:38:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":208,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/77345\/need-help-health-formula-based-on-start-date-end-date-and-100-complete-needed","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77345\/need-help-health-formula-based-on-start-date-end-date-and-100-complete-needed","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-16T22:36:25+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":77291,"type":"question","name":"Aging Formula?","excerpt":"Looking for a formula that would express the following logic: If item has been approved [Approval Date], Count # of days from Approval Date to TODAY. If item has been completed, [Completed Date], Count # of days from Approval Date to Completed Date. I've looked through the forums, but can't find something that appears…","categoryID":322,"dateInserted":"2021-03-15T15:48:58+00:00","dateUpdated":null,"dateLastComment":"2021-03-16T21:44:00+00:00","insertUserID":125759,"insertUser":{"userID":125759,"name":"Guaca Mohle","url":"https:\/\/community.smartsheet.com\/profile\/Guaca%20Mohle","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zxPeMz7Hwf4!3VDfv86wi5Y!xMof-OLUf15","dateLastActive":"2021-03-17T15:15:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":11072,"lastUser":{"userID":11072,"name":"Mike Wilday","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Wilday","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/749H48MC3045\/nF9FQYPTX0X7D.jpg","dateLastActive":"2023-08-23T14:56:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":899,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/77291\/aging-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77291\/aging-formula","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-15T16:10:45+00:00","dateAnswered":"2021-03-15T16:09:13+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":77290,"type":"question","name":"Cursor Bug when Referencing other Sheets in a Formula","excerpt":"Hi, I found a bug when referencing other sheets in a formula. When referencing multiple ranges from other sheets, the cursor will go outside of the braces for the first reference, then will go inside the braces for every reference after. I need to either click outside of the braces or hit the right key to get outside of…","categoryID":322,"dateInserted":"2021-03-15T15:37:41+00:00","dateUpdated":null,"dateLastComment":"2021-03-16T21:42:19+00:00","insertUserID":130394,"insertUser":{"userID":130394,"name":"Andrew James","url":"https:\/\/community.smartsheet.com\/profile\/Andrew%20James","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-03-26T18:57:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":130394,"lastUser":{"userID":130394,"name":"Andrew James","url":"https:\/\/community.smartsheet.com\/profile\/Andrew%20James","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-03-26T18:57:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":94,"score":1,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/77290\/cursor-bug-when-referencing-other-sheets-in-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77290\/cursor-bug-when-referencing-other-sheets-in-a-formula","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-16T21:42:24+00:00","dateAnswered":"2021-03-16T14:08:36+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"},{"tagID":281,"urlcode":"support","name":"Support"},{"tagID":285,"urlcode":"solution-center","name":"Solution Center"}],"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":77349,"type":"question","name":"Formula INDEX\/MATCH is pulling wrong values. Does anyone have similar experience?","excerpt":"Formula used: Sheet it's pulling from: Result: NO MATCH although the code exists exactly the same in both sheets.","categoryID":322,"dateInserted":"2021-03-16T17:59:49+00:00","dateUpdated":"2021-03-16T18:00:28+00:00","dateLastComment":"2021-03-16T20:35:53+00:00","insertUserID":70006,"insertUser":{"userID":70006,"name":"pavla70006","url":"https:\/\/community.smartsheet.com\/profile\/pavla70006","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-04-08T18:36:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":70006,"lastUserID":133131,"lastUser":{"userID":133131,"name":"Sam M.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20M.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-05-27T16:38:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":74,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/77349\/formula-index-match-is-pulling-wrong-values-does-anyone-have-similar-experience","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77349\/formula-index-match-is-pulling-wrong-values-does-anyone-have-similar-experience","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/TN889SHYDKDR\/screen-shot-2021-03-16-at-10-55-27-am.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screen Shot 2021-03-16 at 10.55.27 AM.png"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-16T20:35:53+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":77314,"type":"question","name":"Counting the number of unique values in a column","excerpt":"I feel like this should be simple, but I can't work it out! My smartsheet has 753 rows, but there are only 479 unique values in the column I'm looking at (which I discovered in Excel). How can I count this using a Smartsheet formula? I only want a number. No criteria. Help! Thanks in advance.","categoryID":322,"dateInserted":"2021-03-16T01:45:46+00:00","dateUpdated":null,"dateLastComment":"2021-03-16T20:29:50+00:00","insertUserID":110156,"insertUser":{"userID":110156,"name":"Amy Buckle","url":"https:\/\/community.smartsheet.com\/profile\/Amy%20Buckle","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-04-13T00:48:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":126171,"lastUser":{"userID":126171,"name":"Mark Cronk","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mark%20Cronk","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9UVGVH5TN00I\/nO060PSRF4UVU.jpg","dateLastActive":"2023-05-10T19:07:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":602,"score":null,"hot":8,"url":"https:\/\/community.smartsheet.com\/discussion\/77314\/counting-the-number-of-unique-values-in-a-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77314\/counting-the-number-of-unique-values-in-a-column","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-16T03:38:27+00:00","dateAnswered":"2021-03-16T02:36:03+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":77097,"type":"question","name":"RYG green balls counting weekly","excerpt":"Hello, Could someone please provide a formula to count Green symbols in STATUS column during the past week? I need weekly report of how many green we get during past week . Also should I create special column for counting or I can use existing STATUS column. Thanks in advance. Marat","categoryID":322,"dateInserted":"2021-03-09T22:00:44+00:00","dateUpdated":null,"dateLastComment":"2021-03-16T19:57:41+00:00","insertUserID":132475,"insertUser":{"userID":132475,"name":"Marat Nauanov","url":"https:\/\/community.smartsheet.com\/profile\/Marat%20Nauanov","photoUrl":"https:\/\/lh5.googleusercontent.com\/-juAhsFE1AO8\/AAAAAAAAAAI\/AAAAAAAAAAA\/AMZuucnEE2HO8rxuwvur2hIQhoJA9ZcD_Q\/s96-c\/photo.jpg","dateLastActive":"2021-03-16T20:21:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":132475,"lastUser":{"userID":132475,"name":"Marat Nauanov","url":"https:\/\/community.smartsheet.com\/profile\/Marat%20Nauanov","photoUrl":"https:\/\/lh5.googleusercontent.com\/-juAhsFE1AO8\/AAAAAAAAAAI\/AAAAAAAAAAA\/AMZuucnEE2HO8rxuwvur2hIQhoJA9ZcD_Q\/s96-c\/photo.jpg","dateLastActive":"2021-03-16T20:21:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":12,"countViews":92,"score":null,"hot":12,"url":"https:\/\/community.smartsheet.com\/discussion\/77097\/ryg-green-balls-counting-weekly","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77097\/ryg-green-balls-counting-weekly","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-11T09:58: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":77333,"type":"question","name":"Question regarding automatic date for parent row if all children have a date","excerpt":"This is what I have so far, but it populates the date if one out of (how many children) is filled in. IF(COUNTIFS(CHILDREN(), ISDATE(@cell)) > 0, MIN(CHILDREN()), \"//www.santa-greenland.com/community/categories/formulas-and-functions/\") I would like it to return the last date only if all the children have a date in their respective row. Thank you","categoryID":322,"dateInserted":"2021-03-16T14:29:07+00:00","dateUpdated":null,"dateLastComment":"2021-03-16T19:05:27+00:00","insertUserID":131626,"insertUser":{"userID":131626,"name":"dlreed13","title":"","url":"https:\/\/community.smartsheet.com\/profile\/dlreed13","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hglQoaTU9zo!u4_Rd3yLon4!WCcfYhr95fP","dateLastActive":"2021-10-27T22:23:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":133131,"lastUser":{"userID":133131,"name":"Sam M.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20M.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-05-27T16:38:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":159,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/77333\/question-regarding-automatic-date-for-parent-row-if-all-children-have-a-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77333\/question-regarding-automatic-date-for-parent-row-if-all-children-have-a-date","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-16T18:06:27+00:00","dateAnswered":"2021-03-16T17:10: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":77327,"type":"question","name":"Countif with System Generated Date Column on Separate Sheet","excerpt":"Hello! I have a sheet that all projects that are marked as closed are automatically moved to. There is a system generated date field to show when the lines were created so that we know what date each project was closed. I am trying to, in a separate sheet, reference the Date Closed column and return a countifs if the date…","categoryID":322,"dateInserted":"2021-03-16T13:55:40+00:00","dateUpdated":null,"dateLastComment":"2021-03-16T18:22:40+00:00","insertUserID":133652,"insertUser":{"userID":133652,"name":"Sara Loehwing","url":"https:\/\/community.smartsheet.com\/profile\/Sara%20Loehwing","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-03-16T18:20:54+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":7,"countViews":93,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/77327\/countif-with-system-generated-date-column-on-separate-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77327\/countif-with-system-generated-date-column-on-separate-sheet","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/994FQN7F2NW2\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-16T18:16:16+00:00","dateAnswered":"2021-03-16T18:09:16+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