Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook templateto view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.
❓️Product questions
Sign in andask it here!The community's got your back.

Wondering if there’s a problem?
Check out theStatus Pageand subscribe for notifications.

Check what Support you have access to based on your Plan
Overview of Smartsheet support hours and resources

Share your knowledge!
Help others byanswering questions.

公式and Functions

了一种形式ula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Discussion List

50000, IF(IFERROR(MATCH([Row#]@row, {QC_Results_DB Row#}, 0), 0) > 0, 1, 0)) This formula triggers correctly, and checks the box when it should. The issue is that the workflow isn't…","categoryID":322,"dateInserted":"2020-11-19T22:23:32+00:00","dateUpdated":"2020-11-19T22:24:40+00:00","dateLastComment":"2020-11-20T05:39:08+00:00","insertUserID":16866,"insertUser":{"userID":16866,"name":"L_123","title":"","url":"https:\/\/community.smartsheet.com\/profile\/L_123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T15:32:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":16866,"lastUserID":127276,"lastUser":{"userID":127276,"name":"Aravind GP","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Aravind%20GP","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/MK9641Q8II5B\/n0LFXGX34BAYV.jpg","dateLastActive":"2022-03-08T07:15:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":1543,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/73349\/workflow-not-triggering","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73349\/workflow-not-triggering","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/CLON8WS74M03\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.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"},{"tagID":448,"urlcode":"workflows-in-smartsheet","name":"Workflows in Smartsheet"}],"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":35381,"type":"discussion","name":"CountIF with wild card","excerpt":"I'm trying to count the number of items in a column where part of the text shows. Is there a way to do this? For e.g., I'd like it to count up the number of rows from a column in which the words \"ids\" show up. Keeping in mind this only part of the text (e.g. some rows say \"ids?\", \"ids due\", \"IDS? Client refs\" etc.\" I've…","categoryID":322,"dateInserted":"2018-11-09T00:06:51+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2020-11-20T05:10:26+00:00","insertUserID":65091,"insertUser":{"userID":65091,"name":"Ajr1713","url":"https:\/\/community.smartsheet.com\/profile\/Ajr1713","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-11-10T01:04:43+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":112221,"lastUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-07-14T11:27:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":1782,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/35381\/countif-with-wild-card","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/35381\/countif-with-wild-card","format":"Html","groupID":null,"statusID":0,"attributes":{},"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":274,"urlcode":"Legal","name":"Legal"},{"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":73337,"type":"question","name":"IF Function","excerpt":"Hi, I am trying to create a formula one which there is two calculated values. Here is what I created below. =IF(ColumnName1< ColumnName2 - 3 * ColumnName3, IF(ColumnName3 > ColumnName2@ * 3, \"True\")) Does not work for me... First half of calculation =IF(ColumnName1< ColumnName2 - 3 * ColumnName3 Second half of calculation…","categoryID":322,"dateInserted":"2020-11-19T18:38:23+00:00","dateUpdated":null,"dateLastComment":"2020-11-19T21:17:51+00:00","insertUserID":125565,"insertUser":{"userID":125565,"name":"Allie Gentry","url":"https:\/\/community.smartsheet.com\/profile\/Allie%20Gentry","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4bdZuKGCTrQ!4efa-H3_Tj4!4sw_K34A9UX","dateLastActive":"2021-02-19T15:33:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":77566,"lastUser":{"userID":77566,"name":"Connor Hartford","url":"https:\/\/community.smartsheet.com\/profile\/Connor%20Hartford","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/87JWC0Y5TTDH\/nFI9I6U6FCCOT.jpg","dateLastActive":"2023-07-07T11:40:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":64,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/73337\/if-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73337\/if-function","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-11-19T21:05:11+00:00","dateAnswered":"2020-11-19T19:20: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":73342,"type":"question","name":"SUMIF with FIND","excerpt":"Greetings, =SUMIFS({Commercial MRR}, {Commercial Service Type}, \"10M\", {Commercial Services}, FIND(\"soho\", LOWER({Commercial Services}) > 0)) I tried the above formula that I found the logic in a post from mid 2019, but I wasn't sure if that thread was considered dead so I created this thread. I'm getting an error with the…","categoryID":322,"dateInserted":"2020-11-19T20:01:04+00:00","dateUpdated":null,"dateLastComment":"2020-11-19T21:09:44+00:00","insertUserID":76611,"insertUser":{"userID":76611,"name":"Sinema","url":"https:\/\/community.smartsheet.com\/profile\/Sinema","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-11-20T13:34:07+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-07-13T02:17:09+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":71,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/73342\/sumif-with-find","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73342\/sumif-with-find","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-11-19T21:08:17+00:00","dateAnswered":"2020-11-19T20:28: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":53641,"type":"discussion","name":"SUMIFS & FIND functionality with Summary Sheets","excerpt":"Hi All, I checked the archives and couldn't find a solution to my problem so I apologize if this has already been asked. I am trying to create a formula for my Summary Sheet that will provide the sum of dollar amounts but only if they are \"Cost Savings\" or \"Risk Management\". I am also trying to create a formula for just…","categoryID":322,"dateInserted":"2019-07-24T11:53:02+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2020-11-19T21:07:32+00:00","insertUserID":97411,"insertUser":{"userID":97411,"name":"CL-","url":"https:\/\/community.smartsheet.com\/profile\/CL-","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-07-29T15:27:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":76611,"lastUser":{"userID":76611,"name":"Sinema","url":"https:\/\/community.smartsheet.com\/profile\/Sinema","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-11-20T13:34:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":593,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/53641\/sumifs-find-functionality-with-summary-sheets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/53641\/sumifs-find-functionality-with-summary-sheets","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-07\/7c\/c6\/7cc67289f8d5286af2de0ce21aec9e01.JPG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.JPG"},"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":204,"urlcode":"Forms","name":"Forms"},{"tagID":211,"urlcode":"Manufacturing","name":"Manufacturing"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":265,"urlcode":"Reports","name":"Reports"},{"tagID":391,"urlcode":"product-development","name":"Product Development"}],"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":73336,"type":"question","name":"sum a multi-line text and numbers cell","excerpt":"Hello all, I have a sheet need sum number in multiline cell like: A1 = 80 GB A2 = 100 GB and 1 TB A3 (Total) = 1180 GB or 1.18 TB","categoryID":322,"dateInserted":"2020-11-19T18:16:36+00:00","dateUpdated":"2020-11-19T18:17:55+00:00","dateLastComment":"2020-11-19T21:02:28+00:00","insertUserID":128978,"insertUser":{"userID":128978,"name":"Ahmad","url":"https:\/\/community.smartsheet.com\/profile\/Ahmad","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GjLw6wp21EfGA7QJkvv8CjwZiFZ6n1Aim4llOKD=s96-c","dateLastActive":"2020-11-26T19:52:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":128978,"lastUserID":128978,"lastUser":{"userID":128978,"name":"Ahmad","url":"https:\/\/community.smartsheet.com\/profile\/Ahmad","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GjLw6wp21EfGA7QJkvv8CjwZiFZ6n1Aim4llOKD=s96-c","dateLastActive":"2020-11-26T19:52:15+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":61,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/73336\/sum-a-multi-line-text-and-numbers-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73336\/sum-a-multi-line-text-and-numbers-cell","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":73343,"type":"question","name":"From specific cell end of the sheet","excerpt":"Hi, I have a project template in which I would like to use a formula that allows me to search in a range that starts at a specific cell but ends at the last line of the sheet. In some sheets I have 100 rows and in some sheets I have 500 rows so I can't put, for example, [Actual Material Costs]2:[Actual Material Costs]500…","categoryID":322,"dateInserted":"2020-11-19T20:12:34+00:00","dateUpdated":null,"dateLastComment":"2020-11-19T20:51:19+00:00","insertUserID":119565,"insertUser":{"userID":119565,"name":"Julie Barbeau","url":"https:\/\/community.smartsheet.com\/profile\/Julie%20Barbeau","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14Gif9wXfGhw1gxEXZVVTw5-ZblgcxqeKzkccHIhK","dateLastActive":"2023-04-23T12:32:43+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":1,"countViews":63,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/73343\/from-specific-cell-end-of-the-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73343\/from-specific-cell-end-of-the-sheet","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":73340,"type":"question","name":"Countifs - 2 field ranges plus a date","excerpt":"Hi, I am trying to create a formula to count companies (1st column) that we won a project(2nd shows win\/lose) in certain date ranges (example Q1 - January to April of 2020) I can get the count formula right that counts companies that we won the project with, can't figure out how to narrow it down with the date added into…","categoryID":322,"dateInserted":"2020-11-19T19:30:05+00:00","dateUpdated":null,"dateLastComment":"2020-11-19T20:33:06+00:00","insertUserID":128982,"insertUser":{"userID":128982,"name":"Simon Jordan","url":"https:\/\/community.smartsheet.com\/profile\/Simon%20Jordan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-12-01T19:27:02+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-07-13T02:17:09+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":71,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/73340\/countifs-2-field-ranges-plus-a-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73340\/countifs-2-field-ranges-plus-a-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":[{"tagID":218,"urlcode":"Construction","name":"Construction"},{"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":73339,"type":"question","name":"A leading zero is disrupting my formula.","excerpt":"I have a form that is asking for time. In order to calculate it I break the minutes and seconds out. It is often resulting in answers with a zero in front. This disrupts the formula listed here, =([(H) Hour]1 * 60 * 60) + ([(M) Minute]1 * 60) + [(S) Second]1. We have many operators and have instructed them to stop putting…","categoryID":322,"dateInserted":"2020-11-19T19:22:46+00:00","dateUpdated":null,"dateLastComment":"2020-11-19T19:57:11+00:00","insertUserID":125701,"insertUser":{"userID":125701,"name":"Cory Bush","url":"https:\/\/community.smartsheet.com\/profile\/Cory%20Bush","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-11-19T20:13:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":125701,"lastUser":{"userID":125701,"name":"Cory Bush","url":"https:\/\/community.smartsheet.com\/profile\/Cory%20Bush","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-11-19T20:13:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":184,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/73339\/a-leading-zero-is-disrupting-my-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73339\/a-leading-zero-is-disrupting-my-formula","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/XZTSYKHYEL8O\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.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

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
A Big Thank You! to both Paul and Itai! Yes you have the answer. Deeply appreciated.<\/p>

Rod<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"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}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107571,"type":"question","name":"Yellow Code Not Working in Formula","excerpt":"I have the underlying code in a sheet to look at specific items in a row. The Red and Green Code is working as expected by my yellow code is not. I am assuming it has to do with the way I have sestup the today function I have tried both =TODAY(2) and >=TODAY(2) niether seems to work. IF(AND([Due Date]@row = TODAY(2),…","snippet":"I have the underlying code in a sheet to look at specific items in a row. The Red and Green Code is working as expected by my yellow code is not. I am assuming it has to do with…","categoryID":322,"dateInserted":"2023-07-13T17:28:22+00:00","dateUpdated":null,"dateLastComment":"2023-07-14T11:44:14+00:00","insertUserID":162256,"insertUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtf4bLRCxTWbiShmV4wW7Ah1awahmzvkEsk_gLoMMj4VnA=s96-c","dateLastActive":"2023-07-13T22:10: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-07-14T12:37:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":37,"score":null,"hot":3378609156,"url":"https:\/\/community.smartsheet.com\/discussion\/107571\/yellow-code-not-working-in-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107571\/yellow-code-not-working-in-formula","format":"Rich","lastPost":{"discussionID":107571,"commentID":385241,"name":"Re: Yellow Code Not Working in Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385241#Comment_385241","dateInserted":"2023-07-14T11:44:14+00:00","insertUserID":45516,"insertUser":{"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-07-14T12:37:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-13T21:57:50+00:00","dateAnswered":"2023-07-13T20:00:01+00:00","acceptedAnswers":[{"commentID":385104,"body":"

Try this:<\/p>

=IF(Status@row <> \"Complete\", IF([Due Date]@row< TODAY(), \"Red\", IF([Due Date]@row>= TODAY(3), \"Green\", \"Yellow\"))<\/p>


<\/p>

The main issue with your formula is that you are saying to only flag yellow when the due date is equal to TODAY(2) instead of less than or equal to.<\/p>"},{"commentID":385144,"body":"

There are a number of ways to do this. The most secure way would actually mean a restructure and an OR statement:<\/p>

=IF(Status@row <> \"Complete\", IF(OR([Due Date]@row = \"\", [Due Date]@row.= TODAY(3)), \"Green\", IF([Due Date]@row< TODAY(), \"Red\", \"Yellow\"))<\/p>


<\/p>

I moved the Green argument to be first because sometimes Smartsheet will read a blank cell as being less than a non-blank cell. If we had left it in the original order, this would have meant it would flag as true on the red argument when the due date was blank.<\/p>


<\/p>

Of course there are a number of other ways to do it, but this would be my personally preferred method.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"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}],"tags":[]},{"discussionID":107593,"type":"question","name":"Extracting Question","excerpt":"Hello! How can I extract just the first letter of the below column into a new column? Each cell in the below has a different number of characters. Any help is appreciated. Thank you :) Lindsay","snippet":"Hello! How can I extract just the first letter of the below column into a new column? Each cell in the below has a different number of characters. Any help is appreciated. Thank…","categoryID":322,"dateInserted":"2023-07-13T20:50:12+00:00","dateUpdated":null,"dateLastComment":"2023-07-13T21:23:34+00:00","insertUserID":156589,"insertUser":{"userID":156589,"name":"Lea722","url":"https:\/\/community.smartsheet.com\/profile\/Lea722","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HOdtX3ILhjs!wkjuN7oWOTs!Z5bUXUfUbXP","dateLastActive":"2023-07-13T21:26:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-14T13:09:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3378566626,"url":"https:\/\/community.smartsheet.com\/discussion\/107593\/extracting-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107593\/extracting-question","format":"Rich","lastPost":{"discussionID":107593,"commentID":385162,"name":"Re: Extracting Question","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385162#Comment_385162","dateInserted":"2023-07-13T21:23:34+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-14T13:09:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/ULBUIH0BCZ6J\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-14T08:43:57+00:00","dateAnswered":"2023-07-13T20:55:49+00:00","acceptedAnswers":[{"commentID":385149,"body":"

=LEFT([Column Name]@row, 1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"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}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions