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

\"Complete\", IF([Completion\/ Posting Date]@row > TODAY(10), \"Blue\", IF([Completion\/ Posting Date]@row > TODAY(5), \"Green\", IF([Completion\/ Posting Date]@row > TODAY(2), \"Yellow\",…","categoryID":322,"dateInserted":"2021-03-31T14:22:31+00:00","dateUpdated":null,"dateLastComment":"2021-03-31T17:19:59+00:00","insertUserID":134376,"insertUser":{"userID":134376,"name":"Sarah Daley","url":"https:\/\/community.smartsheet.com\/profile\/Sarah%20Daley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-03-31T17:16:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":134376,"lastUser":{"userID":134376,"name":"Sarah Daley","url":"https:\/\/community.smartsheet.com\/profile\/Sarah%20Daley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-03-31T17:16:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":351,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/77860\/status-rgyb-balls-formula-when-due-tomorrow-and-past-due","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77860\/status-rgyb-balls-formula-when-due-tomorrow-and-past-due","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-31T16:07:43+00:00","dateAnswered":"2021-03-31T15:48:47+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":77817,"type":"question","name":"Column Formula to mimic HLOOKUP?","excerpt":"Hi, Gurus! I have a sheet that calculates 2 values based on whether the calculated columns contain an \"X\" in Row 2: #Actual formula is: =SUMIF([Week 1]$2:[Week 300]$2, \"X\", [Week 1]@row:[Week 300]@row) [has \"X\"] #Forecast formula is: =SUMIF($[Week 1]$2:$[Week 300]$2, \"//www.santa-greenland.com/community/categories/formulas-and-functions/\", $[Week 1]@row:$[Week 300]@row) [no \"X\"] I need to…","categoryID":322,"dateInserted":"2021-03-30T14:58:22+00:00","dateUpdated":"2021-03-30T15:45:43+00:00","dateLastComment":"2021-03-31T16:14:24+00:00","insertUserID":78801,"insertUser":{"userID":78801,"name":"Julie@WD","url":"https:\/\/community.smartsheet.com\/profile\/Julie%40WD","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZKHa2VnlbHw!4eVa-IH-TgQ!4_m_1i5EEU4","dateLastActive":"2023-08-25T19:46:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":78801,"lastUserID":78801,"lastUser":{"userID":78801,"name":"Julie@WD","url":"https:\/\/community.smartsheet.com\/profile\/Julie%40WD","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZKHa2VnlbHw!4eVa-IH-TgQ!4_m_1i5EEU4","dateLastActive":"2023-08-25T19:46:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":209,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/77817\/column-formula-to-mimic-hlookup","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77817\/column-formula-to-mimic-hlookup","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/D14W9JUUEA81\/column-formula-question.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"COLUMN_FORMULA_QUESTION.png"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-31T16:09:47+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":77778,"type":"question","name":"SUMIFS #INCORRECT ARGUMENT SET","excerpt":"Can some please explain the reason for the error? I don't understand what is wrong with what I have typed. =SUMIFS({STD HOURS}, {STATUS}, =\"*** COMPLETE ***\", {COMPLETION DATE}, AND(IFERROR(WEEKNUMBER(@cell), 0) = [Week #]@row, IFERROR(YEAR(@cell), 0) = {YEAR SELECTED})) {STD HOURS} is a column with a formula that returns…","categoryID":322,"dateInserted":"2021-03-29T15:50:34+00:00","dateUpdated":null,"dateLastComment":"2021-03-31T15:15:21+00:00","insertUserID":122890,"insertUser":{"userID":122890,"name":"Preston Murphy","url":"https:\/\/community.smartsheet.com\/profile\/Preston%20Murphy","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zW_Uc3ttzXw!ZgVV2YNJdwQ!JiYsJ4eZaXm","dateLastActive":"2021-06-22T17:32:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":121501,"lastUser":{"userID":121501,"name":"David Fiorino","url":"https:\/\/community.smartsheet.com\/profile\/David%20Fiorino","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!nADVnwtth3Q!e4FUJ_NxIvw!wdiA01MmX-o","dateLastActive":"2023-02-01T22:06:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":197,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/77778\/sumifs-incorrect-argument-set","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77778\/sumifs-incorrect-argument-set","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-29T18:26:07+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":77821,"type":"question","name":"I am trying make send weekly schedule from full planned schedule","excerpt":"Hi, I have full planned PM Schedule. I want to set automation for only next week schedule from the full schedule. How to do that. I am fairly new to the smartsheet.","categoryID":322,"dateInserted":"2021-03-30T15:40:06+00:00","dateUpdated":null,"dateLastComment":"2021-03-31T15:14:22+00:00","insertUserID":134295,"insertUser":{"userID":134295,"name":"Arjun Patel","url":"https:\/\/community.smartsheet.com\/profile\/Arjun%20Patel","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-04-08T18:39:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":134295,"lastUser":{"userID":134295,"name":"Arjun Patel","url":"https:\/\/community.smartsheet.com\/profile\/Arjun%20Patel","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-04-08T18:39:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":61,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/77821\/i-am-trying-make-send-weekly-schedule-from-full-planned-schedule","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77821\/i-am-trying-make-send-weekly-schedule-from-full-planned-schedule","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-31T06:54: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}]},{"discussionID":77715,"type":"question","name":"Conditional Formating based on Time of the day","excerpt":"@Paul Newcome Hey Paul, I was following some of your threads for Formulas with time, and I found one that has been really helpful. But there it seems to be something off. I found your answer under the question \"Calculating time worked for Employees\". But the formula doesn't work well when you are adding minutes to a late…","categoryID":322,"dateInserted":"2021-03-26T00:42:54+00:00","dateUpdated":null,"dateLastComment":"2021-03-31T13:22:30+00:00","insertUserID":125398,"insertUser":{"userID":125398,"name":"Karla Rodriguez","url":"https:\/\/community.smartsheet.com\/profile\/Karla%20Rodriguez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-04-20T21:33:09+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-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":105,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/77715\/conditional-formating-based-on-time-of-the-day","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77715\/conditional-formating-based-on-time-of-the-day","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/3E5CT8AVNF5N\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-30T22:29:18+00:00","dateAnswered":"2021-03-26T15:25:44+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":77776,"type":"question","name":"Auto drag down TODAY formula","excerpt":"Hello! I have a sheet with a column designated to always show the =TODAY(0) formula. However, I noticed that when new rows are added, I have to manually drag down the formula. Is there a way to auto-populate the column and lock it so each new row will always have this formula? Thanks!","categoryID":322,"dateInserted":"2021-03-29T13:38:53+00:00","dateUpdated":null,"dateLastComment":"2021-03-31T12:42:06+00:00","insertUserID":133910,"insertUser":{"userID":133910,"name":"Natalie Caamano","url":"https:\/\/community.smartsheet.com\/profile\/Natalie%20Caamano","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-03-31T12:41:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":133910,"lastUser":{"userID":133910,"name":"Natalie Caamano","url":"https:\/\/community.smartsheet.com\/profile\/Natalie%20Caamano","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-03-31T12:41:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":53,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/77776\/auto-drag-down-today-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77776\/auto-drag-down-today-formula","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-31T12:41:30+00:00","dateAnswered":"2021-03-29T14:54:46+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":77787,"type":"question","name":"Last 7 days with multiple conditions","excerpt":"I have an external sheet named Sheet 1. I want to look at Column 1 to see if it is in the last 7 days and then look at Column 2 to see if it has \"Open\" in the cell. If it meets both conditions, I want it to count it. The 1st part of the formula works by itself: =COUNTIFS({Sheet Range 1}, AND (@cell >= TODAY(-7), @cell <=…","categoryID":322,"dateInserted":"2021-03-29T18:10:22+00:00","dateUpdated":null,"dateLastComment":"2021-03-31T12:25:32+00:00","insertUserID":128829,"insertUser":{"userID":128829,"name":"Justin Mauzy","url":"https:\/\/community.smartsheet.com\/profile\/Justin%20Mauzy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-26T16:29:42+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-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":116,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/77787\/last-7-days-with-multiple-conditions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77787\/last-7-days-with-multiple-conditions","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-29T22:30:39+00:00","dateAnswered":"2021-03-29T20:10:12+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":66868,"type":"question","name":"Using CountM to return Count on Specific Items?","excerpt":"In the CountM Article is states you can collect a total number however I would like a count of Specific Items. For example how many Pears? How many Apples? Is that possible?","categoryID":322,"dateInserted":"2020-03-23T15:30:19+00:00","dateUpdated":null,"dateLastComment":"2021-03-31T12:23:36+00:00","insertUserID":63861,"insertUser":{"userID":63861,"name":"Gaeli","url":"https:\/\/community.smartsheet.com\/profile\/Gaeli","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxYbEITs9UBETD9rp0cvhCiSj_GvHrlN_QP9aiiUNw=s96-c","dateLastActive":"2023-05-21T10:50:31+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-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":651,"score":null,"hot":9,"url":"https:\/\/community.smartsheet.com\/discussion\/66868\/using-countm-to-return-count-on-specific-items","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/66868\/using-countm-to-return-count-on-specific-items","format":"rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-03-23T19:20:16+00:00","dateAnswered":"2020-03-23T18:23:39+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":77809,"type":"question","name":"subtract a number form another sheet","excerpt":"I need to subtract a number from another sheet - cant seem to reference the other sheet please help","categoryID":322,"dateInserted":"2021-03-30T12:43:07+00:00","dateUpdated":null,"dateLastComment":"2021-03-31T09:16:02+00:00","insertUserID":134284,"insertUser":{"userID":134284,"name":"Kevin Attwell","url":"https:\/\/community.smartsheet.com\/profile\/Kevin%20Attwell","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!elFApyFBGfo!!aG-c4PeQFlD","dateLastActive":"2022-08-15T06:50:11+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-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":598,"score":null,"hot":10,"url":"https:\/\/community.smartsheet.com\/discussion\/77809\/subtract-a-number-form-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77809\/subtract-a-number-form-another-sheet","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-03-31T08:47:04+00:00","dateAnswered":"2021-03-31T07:31:18+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":77847,"type":"question","name":"Update request - add field from other sheet","excerpt":"Hi, is there possible to add fields from other sheet (i.e. sheet B) to an update request from a sheet A?","categoryID":322,"dateInserted":"2021-03-31T02:34:22+00:00","dateUpdated":null,"dateLastComment":"2021-03-31T07:11:34+00:00","insertUserID":134348,"insertUser":{"userID":134348,"name":"Titania Suwarto","url":"https:\/\/community.smartsheet.com\/profile\/Titania%20Suwarto","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-08-02T02:15:36+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-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":61,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/77847\/update-request-add-field-from-other-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77847\/update-request-add-field-from-other-sheet","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-03-31T06:18:32+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}">

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-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"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-27T02:16:35+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":25,"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