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.

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

Status [Open In Progress] My Status column is using RYG balls, so \"Red\"=Open and \"Yellow\"=In Progress. I keep getting…","categoryID":322,"dateInserted":"2020-07-08T16:44:24+00:00","dateUpdated":null,"dateLastComment":"2020-07-08T19:11:50+00:00","insertUserID":123940,"insertUser":{"userID":123940,"name":"Jamy Crum","url":"https:\/\/community.smartsheet.com\/profile\/Jamy%20Crum","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-02-02T18:55:48+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-20T03:23:12+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\/69699\/need-help-with-using-a-countifs-with-or","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69699\/need-help-with-using-a-countifs-with-or","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-07-09T09:31:16+00:00","dateAnswered":"2020-07-08T17:08:14+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":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":69612,"type":"question","name":"Ref SUMIF example in Help","excerpt":"Hi, Referring the SUMIF example in the Help article https:\/\/help.smartsheet.com\/function\/sumif, it is not clear to me why =SUMIF([Price Per Unit]1:[Price Per Unit]3, >20, [Transaction Total]1:[Transaction Total]3) has the result 3,115.00, and not 2,303.00, that is 1,491.00 + 812.00 Thank you, Elena","categoryID":322,"dateInserted":"2020-07-05T20:45:47+00:00","dateUpdated":null,"dateLastComment":"2020-07-08T18:30:39+00:00","insertUserID":123558,"insertUser":{"userID":123558,"name":"Elena Codrea","url":"https:\/\/community.smartsheet.com\/profile\/Elena%20Codrea","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-07-08T18:28:25+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":123558,"lastUser":{"userID":123558,"name":"Elena Codrea","url":"https:\/\/community.smartsheet.com\/profile\/Elena%20Codrea","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-07-08T18:28:25+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":72,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/69612\/ref-sumif-example-in-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69612\/ref-sumif-example-in-help","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-07-06T09:00:36+00:00","dateAnswered":"2020-07-06T07:59:01+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":69688,"type":"question","name":"SUMIF for Contact Column Properties","excerpt":"My formula comes back as #No Match but when I filter, I see the results I need. I am hoping you can assist! I am trying to calculate the amount of time spent for a contact person. Here is the formula I have so far: =SUMIF([Contract Phase Assigned to]:[Contract Phase Assigned to], \"Stephanie Bauder\", [Total Time Spent…","categoryID":322,"dateInserted":"2020-07-08T14:44:43+00:00","dateUpdated":null,"dateLastComment":"2020-07-08T17:05:13+00:00","insertUserID":119027,"insertUser":{"userID":119027,"name":"Stephanie Bauder","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20Bauder","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-02-16T17:27:58+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-20T03:23:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":114,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/69688\/sumif-for-contact-column-properties","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69688\/sumif-for-contact-column-properties","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":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":69687,"type":"question","name":"RYG Ball Formula","excerpt":"Can you change formulas to make \"Not Started\" be the same as if it was blank? If I deleted the Not Started lines then my parent row would match my child\/ children rows. (See snip # 2). I'm not sure if it's possible since you can't paste \"Not Started\" into a cell; it just changes to \"Not Started\". My current formula:…","categoryID":322,"dateInserted":"2020-07-08T14:44:34+00:00","dateUpdated":"2020-07-08T14:45:21+00:00","dateLastComment":"2020-07-08T15:52:48+00:00","insertUserID":119188,"insertUser":{"userID":119188,"name":"Josh Canady","url":"https:\/\/community.smartsheet.com\/profile\/Josh%20Canady","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-05-04T14:40:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":119188,"lastUserID":119188,"lastUser":{"userID":119188,"name":"Josh Canady","url":"https:\/\/community.smartsheet.com\/profile\/Josh%20Canady","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-05-04T14:40:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":62,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/69687\/ryg-ball-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69687\/ryg-ball-formula","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/P2HVP2RCGDV2\/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"}],"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":69681,"type":"question","name":"Using CHILDREN for ranges in VLOOKUP or INDEX MATCH","excerpt":"Hoping someone can help with this as it seems possible... I have a sheet for contacts at individual centers across a state. There are roles assigned to the contacts, so for example there may be a director, a supervisor and a coordinator. In the sheet, each parent row is a row with general center information and (the part…","categoryID":322,"dateInserted":"2020-07-08T12:42:36+00:00","dateUpdated":null,"dateLastComment":"2020-07-08T15:47:57+00:00","insertUserID":77746,"insertUser":{"userID":77746,"name":"weswillis","url":"https:\/\/community.smartsheet.com\/profile\/weswillis","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-07-27T15:08:57+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-20T03:23:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":1176,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/69681\/using-children-for-ranges-in-vlookup-or-index-match","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69681\/using-children-for-ranges-in-vlookup-or-index-match","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/VR1DCPRAT6HO\/screen-shot-2020-07-08-at-8-33-23-am.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screen Shot 2020-07-08 at 8.33.23 AM.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-07-08T15:50:54+00:00","dateAnswered":"2020-07-08T15:47: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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":319,"urlcode":"functionality","name":"functionality"}],"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":69676,"type":"question","name":"Counting # of Employees working this week","excerpt":"Hello, I am wanting to count the number of employees that are working this week. However my current formula does not take into account the number of unique names within a weeknumber. Any help would be apreciated.","categoryID":322,"dateInserted":"2020-07-07T23:14:34+00:00","dateUpdated":null,"dateLastComment":"2020-07-08T15:30:45+00:00","insertUserID":122774,"insertUser":{"userID":122774,"name":"Mallory McFall","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mallory%20McFall","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bbPD2z0YVvw!0ujCv-kldiY!Y3o_62EPxu8","dateLastActive":"2021-08-13T15:45:00+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-21T07:06:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":135,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/69676\/counting-of-employees-working-this-week","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69676\/counting-of-employees-working-this-week","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-07-08T15:09:21+00:00","dateAnswered":"2020-07-08T08:39:06+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":69675,"type":"question","name":"SUMIFS function with Auto\/Number system column","excerpt":"Hi all! I'm trying to summarize deliverables using the date stamp of when a form was submitted. Unfortunately the way our form is set up, we have employees fill out a form and enter the month and the year separately so our sheet does not have a normal date column. Is this possible to use the Auto\/Number system column as a…","categoryID":322,"dateInserted":"2020-07-07T22:34:29+00:00","dateUpdated":null,"dateLastComment":"2020-07-08T15:29:04+00:00","insertUserID":123907,"insertUser":{"userID":123907,"name":"Steph Harper","url":"https:\/\/community.smartsheet.com\/profile\/Steph%20Harper","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7_HZe1mPPvw!m0Fa3_H1WPw!PPLrblwh43c","dateLastActive":"2020-07-09T22:40:20+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":123907,"lastUser":{"userID":123907,"name":"Steph Harper","url":"https:\/\/community.smartsheet.com\/profile\/Steph%20Harper","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7_HZe1mPPvw!m0Fa3_H1WPw!PPLrblwh43c","dateLastActive":"2020-07-09T22:40:20+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":66,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/69675\/sumifs-function-with-auto-number-system-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69675\/sumifs-function-with-auto-number-system-column","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-07-08T15:06:28+00:00","dateAnswered":"2020-07-08T14:59:23+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}]}],"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公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
No. In your formula from your post just before my last one, you were adding together 3 different joins. The first one has a different syntax from the 2nd and 3rd. The 2nd and 3rd have the correct syntax already.<\/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":109176,"type":"question","name":"How to check a box if exact text is found within a string of text","excerpt":"Hello, I am trying to place a check in a box when some exact text is found within a string of words. I am looking to place a check when \"TAC\" is found in a string of words. Here's the formula I am using, but its not working. No error message, but is not selecting the checkbox appropriately. =IF(HAS([Column Name1]@row,…","snippet":"Hello, I am trying to place a check in a box when some exact text is found within a string of words. I am looking to place a check when \"TAC\" is found in a string of words. Here's…","categoryID":322,"dateInserted":"2023-08-19T01:53:52+00:00","dateUpdated":null,"dateLastComment":"2023-08-19T17:54:54+00:00","insertUserID":149359,"insertUser":{"userID":149359,"name":"Jen H.","url":"https:\/\/community.smartsheet.com\/profile\/Jen%20H.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!uzlXJ-dimro!oc1UqANVjEQ!GEwnp-NhEjM","dateLastActive":"2023-08-19T18:02:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":149359,"lastUser":{"userID":149359,"name":"Jen H.","url":"https:\/\/community.smartsheet.com\/profile\/Jen%20H.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!uzlXJ-dimro!oc1UqANVjEQ!GEwnp-NhEjM","dateLastActive":"2023-08-19T18:02:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":45,"score":null,"hot":3384878926,"url":"https:\/\/community.smartsheet.com\/discussion\/109176\/how-to-check-a-box-if-exact-text-is-found-within-a-string-of-text","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109176\/how-to-check-a-box-if-exact-text-is-found-within-a-string-of-text","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109176,"commentID":391532,"name":"Re: How to check a box if exact text is found within a string of text","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391532#Comment_391532","dateInserted":"2023-08-19T17:54:54+00:00","insertUserID":149359,"insertUser":{"userID":149359,"name":"Jen H.","url":"https:\/\/community.smartsheet.com\/profile\/Jen%20H.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!uzlXJ-dimro!oc1UqANVjEQ!GEwnp-NhEjM","dateLastActive":"2023-08-19T18:02:04+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-19T17:54:00+00:00","dateAnswered":"2023-08-19T02:33:27+00:00","acceptedAnswers":[{"commentID":391518,"body":"

HAS() will only return true under three conditions.<\/p>

  1. The cell is a multicontact cell and contains a match for the contact<\/li>
  2. The cell is a multiselect dropdown and contains a match<\/li>
  3. The cell is not a multicontact or multiselect dropdown and contains ONLY and EXACTLY the text you are matching against. <\/li><\/ol>

    I am assuming your row in question is a standard Text\/Number cell? If so, you will need to get somewhat creative with some CONTAINS() statements.<\/p>

    This will narrow down your matches to only cells that contain a space both before and after TAC. If, however, TAC can appear as the first or last \"word\" in the cell, it would not match in that circumstance as there would only be a space on one side.<\/p>

    =IF(CONTAINS(\" TAC \", [Column Name1]@row), 1, 0)<\/p>


    <\/p>

    If TAC can appear as the first or last \"word\" in the cell, this will cover those possibilities as well.<\/p>

    =IF(OR(CONTAINS(\" TAC \", [Column Name1]@row), LEFT([Column Name1]@row, 4) = \"TAC \", RIGHT([Column Name1]@row, 4) = \" TAC\"), 1, 0)<\/p>


    <\/p>

    If it is possible that TAC will be the exact entry of the cell, you will need an additional statement to cover that, as there would not be spaces on either side. This will add that option.<\/p>

    =IF(OR(CONTAINS(\" TAC \", [Column Name1]@row), LEFT([Column Name1]@row, 4) = \"TAC \", RIGHT([Column Name1]@row, 4) = \" TAC\", [Column Name1]@row = \"TAC\"), 1, 0)<\/p>


    <\/p>

    If there are other situations you may have in your sheet, i.e., TAC inside parenthesis, or before or after punctuation, etc, you will additional statements to include those options as well.<\/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":109170,"type":"question","name":"COUNTIFS formula with 3 criteria (2 of the criteria have formulas)","excerpt":"Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another dropdown of project names to select from), and Severity (this column displays a \"High\", \"Medium\", or \"Low\" value based on the calculated score in column…","snippet":"Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another…","categoryID":322,"dateInserted":"2023-08-18T20:29:26+00:00","dateUpdated":null,"dateLastComment":"2023-08-18T21:13:12+00:00","insertUserID":165367,"insertUser":{"userID":165367,"name":"Shari D","url":"https:\/\/community.smartsheet.com\/profile\/Shari%20D","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-18T21:12:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-18T21:34:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":109,"score":null,"hot":3384789758,"url":"https:\/\/community.smartsheet.com\/discussion\/109170\/countifs-formula-with-3-criteria-2-of-the-criteria-have-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109170\/countifs-formula-with-3-criteria-2-of-the-criteria-have-formulas","format":"Rich","lastPost":{"discussionID":109170,"commentID":391505,"name":"Re: COUNTIFS formula with 3 criteria (2 of the criteria have formulas)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391505#Comment_391505","dateInserted":"2023-08-18T21:13:12+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-18T21:34:29+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-18T21:12:26+00:00","dateAnswered":"2023-08-18T21:01:48+00:00","acceptedAnswers":[{"commentID":391499,"body":"

    @Shari D<\/a> My apologies, should have been HAS<\/p>

    =COUNTIFS(Status:Status, \"Open\", Severity:Severity, \"High\", [Impacted IT Projects]:[Impacted IT Projects], HAS(@cell, \"IT Project Name 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

    • Using Join(Collect to collect data from multiple sheets to populate in (1) cell

      I am looking to combine data from (3) sheets into one sheet. There will be 3 columns: Team, Assignment, and Notes The cells in the Team and Assignment columns from Sheet1, Sheet2 and Sheet3 are populated from a multi-select dropdown lists. Notes are free type. Example: p-id on the destination sheet is xx, Sheet1 and Sheet2…

    • How to check a box if exact text is found within a string of text

      你好,我am trying to place a check in a box when some exact text is found within a string of words. I am looking to place a check when "TAC" is found in a string of words. Here's the formula I am using, but its not working. No error message, but is not selecting the checkbox appropriately. =IF(HAS([Column Name1]@row,…

    • COUNTIFS formula with 3 criteria (2 of the criteria have formulas)

      Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another dropdown of project names to select from), and Severity (this column displays a "High", "Medium", or "Low" value based on the calculated score in column…