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.
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.
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
0, IF(OR(AND(TODAY() > [Due Date (Planned)]3, [% Complete (Actual)]3 < 1), AND(TODAY() > [Start Date (Planned)]3,…","categoryID":322,"dateInserted":"2018-04-11T16:50:02+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-07-27T12:45:18+00:00","insertUserID":41966,"insertUser":{"userID":41966,"name":"Tony Barthelemy","url":"https:\/\/community.smartsheet.com\/profile\/Tony%20Barthelemy","photoUrl":"https:\/\/lh3.googleusercontent.com\/-XdUIqdMkCWA\/AAAAAAAAAAI\/AAAAAAAAAAA\/4252rscbv5M\/photo.jpg","dateLastActive":"2023-07-26T19:21:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":5895,"lastUser":{"userID":5895,"name":"Steve Rogers","url":"https:\/\/community.smartsheet.com\/profile\/Steve%20Rogers","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!!zLDP82iJzOQ!bEnMW9zZST4!0_vL_69SI0G","dateLastActive":"2021-10-09T23:35:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":556,"score":null,"hot":9,"url":"https:\/\/community.smartsheet.com\/discussion\/23386\/at-risk-flag","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/23386\/at-risk-flag","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}]},{"discussionID":29241,"type":"discussion","name":"Index(Collect add to formula page","excerpt":"Can we get an index(collect example added to the collect formula page? I have found it extremely useful in finding specific iterations of data, and I only stumbled upon it trying random things to solve a specific issue I had a while back. It could have saved me quite a bit of time if I had seen an example of it when…","categoryID":322,"dateInserted":"2018-07-25T15:04:08+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-07-27T12:22:18+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-08-18T20:18:29+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":64,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/29241\/index-collect-add-to-formula-page","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/29241\/index-collect-add-to-formula-page","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":285,"urlcode":"Solution Center","name":"Solution Center"},{"tagID":507,"urlcode":"enhancement-request","name":"Enhancement Request"}],"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":29396,"type":"discussion","name":"Determining an identifier based on parent\/children and an additional factor","excerpt":"I have a sheet where I'm tracking IT initiatives, and some are considered Cross-Platform. Some Cross-Platform records have children and others do not. (See screenshot) Based on some reports I need, I have to identify Cross-Platform initiatives, but do not want to include the child initiatives. I created a column called…","categoryID":322,"dateInserted":"2018-07-26T16:29:50+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-07-27T12:01:00+00:00","insertUserID":55746,"insertUser":{"userID":55746,"name":"SLatchford","url":"https:\/\/community.smartsheet.com\/profile\/SLatchford","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-11-10T16:31:50+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":1,"countViews":107,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/29396\/determining-an-identifier-based-on-parent-children-and-an-additional-factor","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/29396\/determining-an-identifier-based-on-parent-children-and-an-additional-factor","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-07\/e3\/91\/e39141f65a97ff818d8feca559538d15.PNG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"ss HELP.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":234,"urlcode":"Health-care","name":"Healthcare"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}],"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}]},{"discussionID":29401,"type":"discussion","name":"COUNTIFS for individual weeks","excerpt":"I reached out for assistance recently about formulas to count specific projects for individual weeks. The formula assistance I received counts projects from today + next 7 then 13 days then 21 days. What the formulas are not doing is only counting from day 8 to day 13 from today. Here are my formulas...…","categoryID":322,"dateInserted":"2018-07-26T17:06:43+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-07-27T11:55:09+00:00","insertUserID":55141,"insertUser":{"userID":55141,"name":"Steve Fullmer","url":"https:\/\/community.smartsheet.com\/profile\/Steve%20Fullmer","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-07-23T17:06:30+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":1,"countViews":74,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/29401\/countifs-for-individual-weeks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/29401\/countifs-for-individual-weeks","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"}],"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":29451,"type":"discussion","name":"Using formulas to determinate the status of a set of tasks","excerpt":"Hi community, I'm new on Smartsheet and I'm trying to make a formula to automatically calculate the status of a set of tasks (or subtasks). Let me explain my situation: I've created a main task, lets say \"Project1\" and it contains some subtasks as children. Next to this first column, I've got a column for the status (its…","categoryID":322,"dateInserted":"2018-07-27T03:56:31+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-07-27T08:58:48+00:00","insertUserID":55921,"insertUser":{"userID":55921,"name":"Miguel Bravo","url":"https:\/\/community.smartsheet.com\/profile\/Miguel%20Bravo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-07-27T03:45:37+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":1,"countViews":126,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/29451\/using-formulas-to-determinate-the-status-of-a-set-of-tasks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/29451\/using-formulas-to-determinate-the-status-of-a-set-of-tasks","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"}],"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":29181,"type":"discussion","name":"SUMIFS function","excerpt":"I would like to the sum of one column based on a specific date range in another column. e.g Need to total costs for June 2018. I am using the following formula =SUMIFS(Cost3:Cost29,[End Date]3:[End Date]29,”>=01\/06\/2018″,[End Date]3:[End Date]29,”<=30\/06\/2017″) However it is coming back with an error #UNPARSEABLE. ","categoryID":322,"dateInserted":"2018-07-25T00:13:14+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-07-27T08:06:31+00:00","insertUserID":55366,"insertUser":{"userID":55366,"name":"duggo65","url":"https:\/\/community.smartsheet.com\/profile\/duggo65","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-07-24T00:21:40+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":5,"countViews":192,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/29181\/sumifs-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/29181\/sumifs-function","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-07\/dc\/04\/dc04015f18c820157e747c0e7853763e.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Snip20180725_23.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":26551,"type":"discussion","name":"count values where two columns match","excerpt":"i am trying to count the number of matching pairs of values in two columns. eg: Column A Column B 1910 760 1910 810 1910 810 1990 760 1990 760 the outcome of the above that i am looking for is as follows 1910 x 760 = 1 1910 x 810 = 2 1990 x 760 = 2 can someone please help me with a formula that will give me these count…","categoryID":322,"dateInserted":"2018-06-06T18:36:35+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-07-26T18:51:25+00:00","insertUserID":27906,"insertUser":{"userID":27906,"name":"BlairMyles","url":"https:\/\/community.smartsheet.com\/profile\/BlairMyles","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-07-26T18:04:36+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":6,"countViews":529,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/26551\/count-values-where-two-columns-match","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/26551\/count-values-where-two-columns-match","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":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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">
Help Article Resources
Categories
Popular Tags
- Formulas10,666
- Project Management4,364
- Sheets3,711
- Reports2,590
- 形式2375年
- functionality2,145
- Automations1,834
- IT & Operations1,748
- Support1,536
- Enhancement Request1,534
- Cell linking1,531
- Email Notifications1,440
- Account and User Management1,389
- Integrations1,225
- Dashboards1,219
- Conditional Formatting1,200
- Construction1,088
- API and Developers904
- Sharing and Collaboration889
- Manufacturing863
- Workflows in Smartsheet824
- Update Requests814
- Gantt View785
- Product Development783
- Attachments774
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":35,"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>
- The cell is a multicontact cell and contains a match for the contact<\/li>
- The cell is a multiselect dropdown and contains a match<\/li>
- 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":103,"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":"
