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.

Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Discussion List

0, \"Red\", \"Yellow\")) Please help me figure out where the error is. I need to…","categoryID":322,"dateInserted":"2021-01-23T07:00:21+00:00","dateUpdated":null,"dateLastComment":"2021-01-24T04:46:19+00:00","insertUserID":131049,"insertUser":{"userID":131049,"name":"Carol Rykovanova","url":"https:\/\/community.smartsheet.com\/profile\/Carol%20Rykovanova","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-02-16T07:20:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":131049,"lastUser":{"userID":131049,"name":"Carol Rykovanova","url":"https:\/\/community.smartsheet.com\/profile\/Carol%20Rykovanova","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-02-16T07:20:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":257,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/75202\/how-to-sum-up-column-rgy-statuses","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/75202\/how-to-sum-up-column-rgy-statuses","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-01-24T04:31:55+00:00","dateAnswered":"2021-01-24T04:17:59+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":75195,"type":"question","name":"What's wrong with my formula?","excerpt":"=COUNTIFS(Position2:Position196, CONTAINS(\"Medical Assistant\", @cell), [Cleared to Return?]2:[Cleared to Return?]136, OR(CONTAINS(\"No\", @cell), CONTAINS(\"Contingent\", @cell)))","categoryID":322,"dateInserted":"2021-01-22T23:16:08+00:00","dateUpdated":null,"dateLastComment":"2021-01-23T00:52:35+00:00","insertUserID":130636,"insertUser":{"userID":130636,"name":"Grace Tidwell","url":"https:\/\/community.smartsheet.com\/profile\/Grace%20Tidwell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-09-27T15:36:02+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-07-13T20:11:34+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\/75195\/whats-wrong-with-my-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/75195\/whats-wrong-with-my-formula","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":75020,"type":"question","name":"Using Month\/Year in formula","excerpt":"Am I missing an easy way to pull in a count based off month in a chart? For ex. My chart shows July and I would like my formula to count events based off the July name in the chart (@row), I could only figure this out using a month helper column in my source sheet and having to manually adjust each formula? If I used 07\/20…","categoryID":322,"dateInserted":"2021-01-20T02:36:08+00:00","dateUpdated":null,"dateLastComment":"2021-01-22T22:42:24+00:00","insertUserID":123051,"insertUser":{"userID":123051,"name":"Autumn Bennett","url":"https:\/\/community.smartsheet.com\/profile\/Autumn%20Bennett","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-05-10T12:53:06+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":415,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/75020\/using-month-year-in-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/75020\/using-month-year-in-formula","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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Try this:<\/p>

=IF([Employer\/Student Agreement Received Date]@row = \"//www.santa-greenland.com/community/categories/formulas-and-functions/\", IF([ESA Sent Date]@row< TODAY(-30), \"Red\", IF([ESA Sent Date]@row<TODAY(-15), \"Yellow\", \"Green\")), \"Green\")<\/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":107678,"type":"question","name":"Automatic Formula in Add Row Workflow","excerpt":"Hello. I have Sheet A which copies the rows to Sheet B once rows in Sheets A have been approved. Sheet A includes a country column and Sheet B includes both a country and region. I would like the region column to automatically generate the region value based on the country value. I have a separate region and country sheet…","snippet":"Hello. I have Sheet A which copies the rows to Sheet B once rows in Sheets A have been approved. Sheet A includes a country column and Sheet B includes both a country and region.…","categoryID":322,"dateInserted":"2023-07-17T16:45:50+00:00","dateUpdated":null,"dateLastComment":"2023-07-17T17:03:01+00:00","insertUserID":163135,"insertUser":{"userID":163135,"name":"JoannaAU","url":"https:\/\/community.smartsheet.com\/profile\/JoannaAU","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-17T17:03:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":116407,"lastUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/DKTEWPYCU2OY\/nBJQNCZXABT0R.png","dateLastActive":"2023-07-18T04:02:39+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":20,"score":null,"hot":3379227531,"url":"https:\/\/community.smartsheet.com\/discussion\/107678\/automatic-formula-in-add-row-workflow","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107678\/automatic-formula-in-add-row-workflow","format":"Rich","lastPost":{"discussionID":107678,"commentID":385593,"name":"Re: Automatic Formula in Add Row Workflow","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385593#Comment_385593","dateInserted":"2023-07-17T17:03:01+00:00","insertUserID":116407,"insertUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/DKTEWPYCU2OY\/nBJQNCZXABT0R.png","dateLastActive":"2023-07-18T04:02:39+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-17T16:55:04+00:00","dateAnswered":"2023-07-17T16:50:20+00:00","acceptedAnswers":[{"commentID":385587,"body":"

@JoannaAU<\/a> if you create a column formula for the region column in the destination sheet with the lookup functions, it will automatically pull on the matching value when a new row is added.<\/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":107668,"type":"question","name":"How to find if someone is assigned to two tasks on the same day?","excerpt":"Good morning, I have a sheet with due date in one column and team member assigned in another column. I want to ensure no one is assigned two tasks on the same day. I have exhausted my googling abilities and have come up empty. Any ideas here?","snippet":"Good morning, I have a sheet with due date in one column and team member assigned in another column. I want to ensure no one is assigned two tasks on the same day. I have…","categoryID":322,"dateInserted":"2023-07-17T15:23:13+00:00","dateUpdated":null,"dateLastComment":"2023-07-17T16:01:55+00:00","insertUserID":163551,"insertUser":{"userID":163551,"name":"FBowman","title":"Business Operations and Underwriting Assistant Manager","url":"https:\/\/community.smartsheet.com\/profile\/FBowman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-17T15:36:14+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-17T19:39:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3379218908,"url":"https:\/\/community.smartsheet.com\/discussion\/107668\/how-to-find-if-someone-is-assigned-to-two-tasks-on-the-same-day","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107668\/how-to-find-if-someone-is-assigned-to-two-tasks-on-the-same-day","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107668,"commentID":385584,"name":"Re: How to find if someone is assigned to two tasks on the same day?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385584#Comment_385584","dateInserted":"2023-07-17T16:01:55+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-17T19:39:07+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-17T15:36:23+00:00","dateAnswered":"2023-07-17T15:30:36+00:00","acceptedAnswers":[{"commentID":385572,"body":"

You are basically looking for duplicates. Try something like this in a flag type column:<\/p>

=IF(COUNTIFS([Assigned To]:[Assigned To], @cell = [Assigned To]@row, [Due Date]:[Due Date], @cell = [Due Date]@row)> 1, 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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"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

  • Formula to set red/yellow/green status based on information in two fields.

    I am trying to set a field to red if an agreement has been sent 30 or more days ago but the received date is blank. I used the following two formulas, but both times, I get the unpassable error. Can you help me figure out what I'm doing wrong? =IF(AND([ESA Sent Date]@row+30(),[Employer/Student Agreement Received Date]@row…

  • Automatic Formula in Add Row Workflow

    Hello. I have Sheet A which copies the rows to Sheet B once rows in Sheets A have been approved. Sheet A includes a country column and Sheet B includes both a country and region. I would like the region column to automatically generate the region value based on the country value. I have a separate region and country sheet…

  • How to find if someone is assigned to two tasks on the same day?

    Good morning, I have a sheet with due date in one column and team member assigned in another column. I want to ensure no one is assigned two tasks on the same day. I have exhausted my googling abilities and have come up empty. Any ideas here?