Formulas and Functions

[Date Due]@row), \"Red\", IF(AND([%…","categoryID":322,"dateInserted":"2021-03-09T16:10:04+00:00","dateUpdated":null,"dateLastComment":"2021-03-10T14:52:03+00:00","insertUserID":132967,"insertUser":{"userID":132967,"name":"Joanna Lupker","url":"https:\/\/community.smartsheet.com\/profile\/Joanna%20Lupker","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-03-23T20:40:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":131402,"lastUser":{"userID":131402,"name":"Heather Duff","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Heather%20Duff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nH14DQG3YON84.png","dateLastActive":"2023-05-03T21:04:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":136,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/77063\/another-status-light-formula-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77063\/another-status-light-formula-question","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/YAGM68O0UF4U\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-10T14:38:06+00:00","dateAnswered":"2021-03-09T19:58:05+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":77112,"type":"question","name":"Different approvals","excerpt":"Hello All I have a question, i have a sheet and approval workflow is triggered when a new item is created. There are two approvals that are needed coming from two cell, and i want to see to overal Status of the approval. I am using formual: =IFERROR(IF(OR([BSS approval]@row = \"Rejected\" , [Function Head Decision]@row =…","categoryID":322,"dateInserted":"2021-03-10T10:23:56+00:00","dateUpdated":null,"dateLastComment":"2021-03-10T13:29:34+00:00","insertUserID":133010,"insertUser":{"userID":133010,"name":"Mario Rocco","url":"https:\/\/community.smartsheet.com\/profile\/Mario%20Rocco","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-07-20T07:41:18+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":131402,"lastUser":{"userID":131402,"name":"Heather Duff","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Heather%20Duff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nH14DQG3YON84.png","dateLastActive":"2023-05-03T21:04:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":61,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/77112\/different-approvals","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77112\/different-approvals","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-10T13:14:51+00:00","dateAnswered":"2021-03-10T12:29:18+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":77103,"type":"question","name":"IF, THEN for stock inventory","excerpt":"Hi, I'm really new to this but couldn't seem to see in the forums - I've set up a PPE stock request form for the teams to fill out - which autopopulates in the respective columns, including the multi-select PPE column. What I'm trying to do is set up an IF something is requested, THEN the columns which has the current…","categoryID":322,"dateInserted":"2021-03-10T00:29:05+00:00","dateUpdated":"2021-03-10T00:45:42+00:00","dateLastComment":"2021-03-10T12:45:08+00:00","insertUserID":133001,"insertUser":{"userID":133001,"name":"Deb Lee","url":"https:\/\/community.smartsheet.com\/profile\/Deb%20Lee","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-05-30T00:31:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":133001,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":61,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/77103\/if-then-for-stock-inventory","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77103\/if-then-for-stock-inventory","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/46Q5DIS3Y3NZ\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-10T12:45:08+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":77034,"type":"question","name":"Formula to always reference the parent of a child row within a different column?","excerpt":"Hello, I am trying using the parent\/child relationship to group a set of data by month and am using the the formula to rank the children rows by their value: =IFERROR(RANKEQ(Value@row, CHILDREN(Value$105), 0), \"//www.santa-greenland.com/community/categories/formulas-and-functions/\") But I was wondering if there is a way to make this as a column formula? I would like it to always rank the…","categoryID":322,"dateInserted":"2021-03-08T19:31:17+00:00","dateUpdated":null,"dateLastComment":"2021-03-10T12:34:22+00:00","insertUserID":114376,"insertUser":{"userID":114376,"name":"Ryan Nix","url":"https:\/\/community.smartsheet.com\/profile\/Ryan%20Nix","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!woHWN18UO34!27lVf7MI5Lw!9UOHK-mEyb4","dateLastActive":"2021-03-11T02:56:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":567,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/77034\/formula-to-always-reference-the-parent-of-a-child-row-within-a-different-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77034\/formula-to-always-reference-the-parent-of-a-child-row-within-a-different-column","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-11T02:56:11+00:00","dateAnswered":"2021-03-09T13:19:37+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":77110,"type":"question","name":"End Date is auto-filled, how to stop?","excerpt":"Hi All, I have created a sheet for my team and outside of normal columns, we have an extra \"Target End Date\" to track if we meet target or not. Please see picture. However, the Actual End Date is auto-filled every time I enter Start Date with the default value of the same day as Start Date. It is very inconvenient, I am…","categoryID":322,"dateInserted":"2021-03-10T09:13:06+00:00","dateUpdated":null,"dateLastComment":"2021-03-10T11:20:13+00:00","insertUserID":132732,"insertUser":{"userID":132732,"name":"Ngan Bui","url":"https:\/\/community.smartsheet.com\/profile\/Ngan%20Bui","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5NDf-WqfbeQ!zCdes9ahyDo!mH5_LnmH7ac","dateLastActive":"2022-04-05T15:08:05+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":132606,"lastUser":{"userID":132606,"name":"Antonio Figueroa","url":"https:\/\/community.smartsheet.com\/profile\/Antonio%20Figueroa","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-23T13:15:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":354,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/77110\/end-date-is-auto-filled-how-to-stop","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77110\/end-date-is-auto-filled-how-to-stop","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/HHB1MFOMXE9S\/end-date-ss.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"end date SS.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-10-13T12:02:26+00:00","dateAnswered":"2021-03-10T11:20: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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}],"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":77108,"type":"question","name":"Formula to evaluate the output of a calculation and to determine a score according to a range","excerpt":"I am currently setting up a scorecard where the individual is allocated points based on % achievements within specific ranges, and would really appreciate some help in formularizing the allocation of points (score) based on the % achievement For example - In the example below. an Achievement of 93% will score 20 points,…","categoryID":322,"dateInserted":"2021-03-10T06:19:59+00:00","dateUpdated":null,"dateLastComment":"2021-03-10T08:33:23+00:00","insertUserID":133007,"insertUser":{"userID":133007,"name":"Lee (Testing) Ziervogel","url":"https:\/\/community.smartsheet.com\/profile\/Lee%20%28Testing%29%20Ziervogel","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hslM4fTc_To!ZODMWQzZZnY!TN38lDveaZ9","dateLastActive":"2021-03-25T14:09:21+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-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":52,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/77108\/formula-to-evaluate-the-output-of-a-calculation-and-to-determine-a-score-according-to-a-range","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77108\/formula-to-evaluate-the-output-of-a-calculation-and-to-determine-a-score-according-to-a-range","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/V385O9Q2Y0TS\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-10T08:25:07+00:00","dateAnswered":"2021-03-10T07:35: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":68968,"type":"question","name":"Any workaround on UTC time?","excerpt":"I was trying to capture the sum of daily submission on the sheet summary base on the system created date, however, it was affected by the UTC time and information capture was not accurate. Any workaround by not getting end user to submit with a date?","categoryID":322,"dateInserted":"2020-06-09T04:07:35+00:00","dateUpdated":"2020-06-09T04:10:52+00:00","dateLastComment":"2021-03-10T00:58:49+00:00","insertUserID":56436,"insertUser":{"userID":56436,"name":"flashock","url":"https:\/\/community.smartsheet.com\/profile\/flashock","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-08\/67\/25\/n6725ef6336f7aadcc4546b28fc2e036e.jpg","dateLastActive":"2023-01-30T01:05:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":56436,"lastUserID":56436,"lastUser":{"userID":56436,"name":"flashock","url":"https:\/\/community.smartsheet.com\/profile\/flashock","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-08\/67\/25\/n6725ef6336f7aadcc4546b28fc2e036e.jpg","dateLastActive":"2023-01-30T01:05:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":367,"score":null,"hot":11,"url":"https:\/\/community.smartsheet.com\/discussion\/68968\/any-workaround-on-utc-time","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/68968\/any-workaround-on-utc-time","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-10T00:57:33+00:00","dateAnswered":"2021-03-09T15:36:57+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":77047,"type":"question","name":"Sheet Summary Formula","excerpt":"Asking for help again with a formula. I need to create a formula in Sheet Summary that rolls up how many participants from each of the 10 columns above attended which \"Program Type.\" Program Type is a dropdown. For context: How many Blue Ridge participants attended \"PSUDS ECHO.\" Wondering if the structure of the sheet is…","categoryID":322,"dateInserted":"2021-03-09T00:17:30+00:00","dateUpdated":null,"dateLastComment":"2021-03-10T00:36:58+00:00","insertUserID":132867,"insertUser":{"userID":132867,"name":"Kayla Rice","url":"https:\/\/community.smartsheet.com\/profile\/Kayla%20Rice","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!u2_W5_t4nXw!ZPdW2ddlZro!cus_tyhBOcx","dateLastActive":"2021-12-20T18:41:57+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":3,"countViews":179,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/77047\/sheet-summary-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77047\/sheet-summary-formula","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/S4DNRTZEKGGP\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-09T20:39:18+00:00","dateAnswered":"2021-03-09T01:23:40+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":335,"urlcode":"sheet-summary","name":"Sheet Summary"}],"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