Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template查看100 +公式,包括词汇的夏娃ry 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.

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

[Current Monthly Usage]7, [ Average 6mths]7 * 0.5 < [Current Monthly Usage]7), 1, 0) I am just trying to return 1 if between 50 and 150% and 0 of outside that range","categoryID":322,"dateInserted":"2018-09-20T18:08:30+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-10-01T21:44:50+00:00","insertUserID":61891,"insertUser":{"userID":61891,"name":"michaeld","url":"https:\/\/community.smartsheet.com\/profile\/michaeld","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-10-01T21:09:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":61891,"lastUser":{"userID":61891,"name":"michaeld","url":"https:\/\/community.smartsheet.com\/profile\/michaeld","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-10-01T21:09:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":63,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/32331\/if-or-argument","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/32331\/if-or-argument","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":211,"urlcode":"manufacturing","name":"Manufacturing"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"}],"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":32236,"type":"discussion","name":"Reference the Children of the Parent of a Cell as the Range","excerpt":"I'm looking for a way to auto number my WBS. I believe I have found a way using logic functions combined with the Match function. The issue I am running into is that I am unable to reference a cell, say Task5 and then specify its \"siblings\" as the range. I have tried to specify the range as Children(Parent(Task5)), but I…","categoryID":322,"dateInserted":"2018-09-19T15:26:49+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-10-01T15:12:37+00:00","insertUserID":61631,"insertUser":{"userID":61631,"name":"Jacob Slaathaug","url":"https:\/\/community.smartsheet.com\/profile\/Jacob%20Slaathaug","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-07\/d5\/41\/nd541693181f7f24b4e3aeca39d5a2d31.jpg","dateLastActive":"2021-03-08T16:53:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":195,"lastUser":{"userID":195,"name":"Shaine Greenwood","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Shaine%20Greenwood","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/198\/nBSBY6VR571R7.jpg","dateLastActive":"2021-08-30T16:03:52+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":412,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/32236\/reference-the-children-of-the-parent-of-a-cell-as-the-range","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/32236\/reference-the-children-of-the-parent-of-a-cell-as-the-range","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-09\/e7\/86\/e786e825fd804a1c06770b86e6b2c47d.PNG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"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"}],"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":32396,"type":"discussion","name":"Formulas don't calculate when cell containing formula is linked","excerpt":"I have a sheet that totals the row values when a row is added from a form. It worked well until the cell containing the formula was linked using vlookup to a separate sheet that is the source of a dashboard. Now it will not calculate and the sheet that references the cell will not be displayed in the fo","categoryID":322,"dateInserted":"2018-09-21T17:00:01+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-10-01T13:26:26+00:00","insertUserID":3247,"insertUser":{"userID":3247,"name":"Melissa Campbell","url":"https:\/\/community.smartsheet.com\/profile\/Melissa%20Campbell","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pyDC6Q78oHQ!3K_Bf_6Ij3w!qtAm6O-QXxM","dateLastActive":"2022-08-16T11:27:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":195,"lastUser":{"userID":195,"name":"Shaine Greenwood","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Shaine%20Greenwood","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/198\/nBSBY6VR571R7.jpg","dateLastActive":"2021-08-30T16:03:52+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":123,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/32396\/formulas-dont-calculate-when-cell-containing-formula-is-linked","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/32396\/formulas-dont-calculate-when-cell-containing-formula-is-linked","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":472,"urlcode":"cell-linking","name":"Cell linking"}],"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":32736,"type":"discussion","name":"Using decision symbols to change text","excerpt":"Please can somebody help me, I'm new to formulas! I'm trying to get text in a cell to change depending on what decision symbol has bee selected (YES, NO, HOLD) I managed to get it working with just 'YES' as follows: =IF(Graphic6 = \"YES\", \"Includes GRAPHICS see schedule\") but I also want different text \"No GRAPHICS\" to…","categoryID":322,"dateInserted":"2018-09-28T00:59:56+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-10-01T02:41:47+00:00","insertUserID":62706,"insertUser":{"userID":62706,"name":"tim@gyroscope","url":"https:\/\/community.smartsheet.com\/profile\/tim%40gyroscope","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mwjNH-zx3yY!3ZtA_5auknw!zTKiQ_JiPmQ","dateLastActive":"2020-08-04T18:55:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":24411,"lastUser":{"userID":24411,"name":"Marcin P","url":"https:\/\/community.smartsheet.com\/profile\/Marcin%20P","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-09\/b9\/54\/nb954f24aea6d9da6778d1e49f99fa373.jpg","dateLastActive":"2019-04-09T01:27:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":220,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/32736\/using-decision-symbols-to-change-text","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/32736\/using-decision-symbols-to-change-text","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":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}]}],"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 theFormula Handbook template!
Hi, <\/p>

Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>

=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>

Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+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-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+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-08-26T01:04:51+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-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"

Try this:<\/p>

=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"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":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"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":"✭✭✭✭✭✭"}},"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-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/categories/formulas-and-functions/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/categories/formulas-and-functions/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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