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.
[Max Story Points]@row, \"Red\"))) Thanks","categoryID":322,"dateInserted":"2020-10-15T21:03:47+00:00","dateUpdated":null,"dateLastComment":"2020-10-15T22:41:23+00:00","insertUserID":111176,"insertUser":{"userID":111176,"name":"Anthony Barthelemy","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20Barthelemy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-01-24T21:26:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":121890,"lastUser":{"userID":121890,"name":"Ramzi K","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ramzi%20K","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/SANQ6N8O1FSK\/n1MST7KW5A43V.png","dateLastActive":"2023-07-13T13:13:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":72,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/72293\/ryg-formula-trigger-yellow-within-2-of-the-set-threshold","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72293\/ryg-formula-trigger-yellow-within-2-of-the-set-threshold","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-10-21T21:26:04+00:00","dateAnswered":"2020-10-15T22:41: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":[],"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":72294,"type":"question","name":"Help with Countifs\/Sumproduct Formula","excerpt":"UPDATE: The 10 should be 7. (1*5+1*2) I have a 2 axis grid. The Cells inside the grid all have letter abbreviations. I'm trying to do subtotals of each row. How can I make a formula that will sum the products of two rows, when one of the rows has a specific character in the cell? On Day 1 There are 5x BLUE team that are W…","categoryID":322,"dateInserted":"2020-10-15T21:14:54+00:00","dateUpdated":"2020-10-15T22:05:58+00:00","dateLastComment":"2020-10-15T22:37:14+00:00","insertUserID":127648,"insertUser":{"userID":127648,"name":"Jason Elias","url":"https:\/\/community.smartsheet.com\/profile\/Jason%20Elias","photoUrl":"https:\/\/lh6.googleusercontent.com\/-M9LFbZ_0DRg\/AAAAAAAAAAI\/AAAAAAAAAAA\/AMZuucnGBPu4Nnl5P6BeTYircmu23GLvbw\/s96-c\/photo.jpg","dateLastActive":"2020-10-15T22:17:42+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":127648,"lastUserID":121890,"lastUser":{"userID":121890,"name":"Ramzi K","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ramzi%20K","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/SANQ6N8O1FSK\/n1MST7KW5A43V.png","dateLastActive":"2023-07-13T13:13:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":193,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/72294\/help-with-countifs-sumproduct-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72294\/help-with-countifs-sumproduct-formula","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/DR5SPJI2V7GP\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}]},{"discussionID":72297,"type":"question","name":"The words in my smartsheet forms are broken","excerpt":"My smartsheet form has words that look broken even though they look alright when I type them in. It breaks words like all to a ll . why is this happening? How can I resolve this issue?","categoryID":322,"dateInserted":"2020-10-15T22:07:41+00:00","dateUpdated":null,"dateLastComment":"2020-10-15T22:10:26+00:00","insertUserID":93606,"insertUser":{"userID":93606,"name":"smart sheet user","url":"https:\/\/community.smartsheet.com\/profile\/smart%20sheet%20user","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!n0Fan3HhgXw!nRlbH7Gxj7w!SOXb_YmrnI_","dateLastActive":"2021-02-22T17:46:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":121890,"lastUser":{"userID":121890,"name":"Ramzi K","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ramzi%20K","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/SANQ6N8O1FSK\/n1MST7KW5A43V.png","dateLastActive":"2023-07-13T13:13:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":41,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/72297\/the-words-in-my-smartsheet-forms-are-broken","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72297\/the-words-in-my-smartsheet-forms-are-broken","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":72296,"type":"question","name":"Nested IFs to populate Priority based on set number of days prior to start date","excerpt":"I'm trying to populate a Priority column with Urgent (within 1 week from today), High (1 - 3 weeks from today), Med (3 - 4 weeks from today), Low (4+ weeks from today), Unknown (no date provided) based on the # of weeks out for the start date of the effort. Both of these work for just 1 Date Range: =IF({Sheet Range 9} <=…","categoryID":322,"dateInserted":"2020-10-15T21:22:01+00:00","dateUpdated":null,"dateLastComment":"2020-10-15T21:48:51+00:00","insertUserID":127636,"insertUser":{"userID":127636,"name":"Erin Aten","url":"https:\/\/community.smartsheet.com\/profile\/Erin%20Aten","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-11-10T19:20:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":127636,"lastUser":{"userID":127636,"name":"Erin Aten","url":"https:\/\/community.smartsheet.com\/profile\/Erin%20Aten","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-11-10T19:20:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":72,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/72296\/nested-ifs-to-populate-priority-based-on-set-number-of-days-prior-to-start-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72296\/nested-ifs-to-populate-priority-based-on-set-number-of-days-prior-to-start-date","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-10-22T16:04:12+00:00","dateAnswered":"2020-10-15T21:40:36+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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">
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 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 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":"
