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

0, Cell1:Cell5) but in doing so only managed to add all positive…","categoryID":322,"dateInserted":"2019-05-17T11:36:16+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-21T08:26:30+00:00","insertUserID":87411,"insertUser":{"userID":87411,"name":"MikeCorvin","url":"https:\/\/community.smartsheet.com\/profile\/MikeCorvin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-05\/42\/33\/n4233c7d40e7a191399104b13ab95b04b.jpg","dateLastActive":"2019-12-30T13:02:01+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":7,"countViews":103,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/48471\/adding-positive-parents","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/48471\/adding-positive-parents","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":238,"urlcode":"finance","name":"Finance"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":285,"urlcode":"solution-center","name":"Solution Center"},{"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":48446,"type":"discussion","name":"SUMIF condition","excerpt":"Hi guys! I have to calculate average in my sheet and I will use: SUMIF \/ COUNTIF - COUNTIF formula is working. - SUMIF formula is not working, i am getting #INVALID OPERATION The SUMIF formula is (screenshot attached) =SUMIF([Assigned to in Region]8:[Assigned to in Region]136, \"Sarah Mabiletsa\", [Quote Prep Cycle…","categoryID":322,"dateInserted":"2019-05-17T04:48:27+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-21T08:04:42+00:00","insertUserID":88951,"insertUser":{"userID":88951,"name":"Fede","url":"https:\/\/community.smartsheet.com\/profile\/Fede","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-05\/13\/ca\/n13ca51b02e7aae7f0cd80d959f4d10c5.JPG","dateLastActive":"2022-01-27T08:53:18+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":6,"countViews":73,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/48446\/sumif-condition","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/48446\/sumif-condition","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-05\/55\/b1\/55b126ce0c3a18a23eecc7752a6b7a3e.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"smart1.jpg"},"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":247,"urlcode":"contacts","name":"Contacts"},{"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":48316,"type":"discussion","name":"LOOKUP Formula based on result between date range","excerpt":"Hello, I've tried to create a LOOKUP formula that will provide the \"Approved Value\" if the \"Date Certified\" calls between the period stipulated in \"Period Start\" and \"Period End,\" without much success. Based on the screen-grab, only two values should be returned if the formula is successful. (I've manually entered these to…","categoryID":322,"dateInserted":"2019-05-15T20:07:58+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-21T08:03:46+00:00","insertUserID":87801,"insertUser":{"userID":87801,"name":"Richard Heath","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Richard%20Heath","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/PQ5MK3GA3T0T\/nRH0VLM6IQ91L.jpg","dateLastActive":"2023-08-25T00:43: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-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":1759,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/48316\/lookup-formula-based-on-result-between-date-range","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/48316\/lookup-formula-based-on-result-between-date-range","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-05\/4c\/60\/4c60197883c69ebbf582ecdeee4a5273.JPG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.JPG"},"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"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}]},{"discussionID":48646,"type":"discussion","name":"Nested IF and","excerpt":"I trying to use to create a sheet that I will use to determine the status by comparing 2 status and then give an answer the e.g. If the status1 is low and status 2 is low the actual status should be low, I used the following IF\/AND Statement and it worked fine: =IF(AND([Status1]3 = \"Low\"; [Status2]3 = \"Low\"); \"Low\"; \" \")…","categoryID":322,"dateInserted":"2019-05-20T16:43:50+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-21T06:01:21+00:00","insertUserID":89271,"insertUser":{"userID":89271,"name":"nkosikhona.msani89271","url":"https:\/\/community.smartsheet.com\/profile\/nkosikhona.msani89271","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-08-28T08:47:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":89271,"lastUser":{"userID":89271,"name":"nkosikhona.msani89271","url":"https:\/\/community.smartsheet.com\/profile\/nkosikhona.msani89271","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-08-28T08:47:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":83,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/48646\/nested-if-and","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/48646\/nested-if-and","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":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":281,"urlcode":"support","name":"Support"},{"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":43751,"type":"discussion","name":"Adding Months to a Date","excerpt":"The Date formula allows addition but I get errors when I cross months by adding days or when I cross years by adding months. (Haven't tested years with adding days but that also crosses a month so I would get an error anyway. I need it to act like Excel and actually add a year for each multiple of 12 months in particular.…","categoryID":322,"dateInserted":"2019-03-15T16:16:13+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-21T02:38:30+00:00","insertUserID":62431,"insertUser":{"userID":62431,"name":"Wilbsguy","url":"https:\/\/community.smartsheet.com\/profile\/Wilbsguy","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AjNdn4RyuDo!oVDd6IxLkXY!2SbSZKUgcSj","dateLastActive":"2021-06-10T22:32:10+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":3,"countViews":2854,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/43751\/adding-months-to-a-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43751\/adding-months-to-a-date","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":234,"urlcode":"health-care","name":"Healthcare"},{"tagID":238,"urlcode":"finance","name":"Finance"},{"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":48611,"type":"discussion","name":"SCC Nesting Summary Data","excerpt":"Is it going to cause any issues if I nest (indent a couple levels) data inside a sub-category, inside of a main category? (see attached img)","categoryID":322,"dateInserted":"2019-05-20T12:16:08+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-20T16:10:14+00:00","insertUserID":58936,"insertUser":{"userID":58936,"name":"Ezra","url":"https:\/\/community.smartsheet.com\/profile\/Ezra","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/722\/n8VN7BXAAHKNJ.png","dateLastActive":"2020-07-17T14:29:53+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":4,"countViews":82,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/48611\/scc-nesting-summary-data","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/48611\/scc-nesting-summary-data","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-05\/c6\/ce\/c6ce440845395dd95b8d6c45d207c0fc.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"SCC_NestedSummaryData.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":215,"urlcode":"templates","name":"Templates"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}],"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}]},{"discussionID":48561,"type":"discussion","name":"JOIN, COLLECT with MATCH","excerpt":"In Sheet 2, I want to JOIN and COLLECT a group of values from Sheet 1, separated by a comma. I have this figured out: =JOIN(COLLECT({AutomatedCourseCopy-CRN}, {AutomatedCourseCopy-CRN}, <>\"//www.santa-greenland.com/community/categories/formulas-and-functions/\", {AutomatedCourseCopy-UseinBCC}, <>\"//www.santa-greenland.com/community/categories/formulas-and-functions/\", {AutomatedCourseCopy-College}, CRN1), \", \") + \",\" I have a set of values in Sheet 3 that…","categoryID":322,"dateInserted":"2019-05-19T16:47:49+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-20T15:00:02+00:00","insertUserID":74206,"insertUser":{"userID":74206,"name":"Art Schneiderheinze","url":"https:\/\/community.smartsheet.com\/profile\/Art%20Schneiderheinze","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/PI7JNYDF7Q80\/nTL55D89VG7YK.png","dateLastActive":"2022-03-03T01:36:25+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":1,"countViews":794,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/48561\/join-collect-with-match","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/48561\/join-collect-with-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":219,"urlcode":"sheets","name":"Sheets"},{"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":48241,"type":"discussion","name":"Gantt Task Completion bar based on dynamic % Complete","excerpt":"Is there a way to set a dynamic column to be the % Complete column for the purpose of setting the Task Completion bar? All help is appreciated.","categoryID":322,"dateInserted":"2019-05-15T11:33:23+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-20T10:20:02+00:00","insertUserID":86741,"insertUser":{"userID":86741,"name":"Ben Donahue","url":"https:\/\/community.smartsheet.com\/profile\/Ben%20Donahue","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-05\/7d\/c7\/n7dc76a8f588de66bb9113372bae7271f.jpg","dateLastActive":"2020-11-16T20:04:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":45236,"lastUser":{"userID":45236,"name":"Alejandra","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Alejandra","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-04\/8a\/fe\/n8afeceb592ad5845f46c7d931defba07.JPG","dateLastActive":"2020-05-15T18:56:51+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":62,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/48241\/gantt-task-completion-bar-based-on-dynamic-complete","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/48241\/gantt-task-completion-bar-based-on-dynamic-complete","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":439,"urlcode":"gantt-view","name":"Gantt View"}],"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":48371,"type":"discussion","name":"Use Countif to count the number of tasks assigned to a worker?","excerpt":"Hi, I'm sure there is a quite dimple solution to the this, but I am having trouble. I want to count the number of rows assigned to each contact, and I used the formula below: =COUNTIF(Assigned Work:Assigned Worker, \"contactname\") The formula returns the error #UNPARSEBALE How can I fix this?","categoryID":322,"dateInserted":"2019-05-16T12:18:24+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-17T12:32:25+00:00","insertUserID":88841,"insertUser":{"userID":88841,"name":"Valencia C.","url":"https:\/\/community.smartsheet.com\/profile\/Valencia%20C.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!87la97H7xLw!ZYtbWeG5dbQ!CDrWQoP3fAT","dateLastActive":"2022-06-07T04:26:57+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":11,"countViews":1001,"score":null,"hot":11,"url":"https:\/\/community.smartsheet.com\/discussion\/48371\/use-countif-to-count-the-number-of-tasks-assigned-to-a-worker","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/48371\/use-countif-to-count-the-number-of-tasks-assigned-to-a-worker","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":210,"urlcode":"human-resources","name":"Human Resources"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}],"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi, <\/p>

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

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

Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"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-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"

Try this:<\/p>

=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"

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

Hi, <\/p>

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

Try something like this.<\/p>

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

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

I hope that helps!<\/p>

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

Best,<\/p>

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

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions