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, it returns an error. What would be the best method for moving the formula to the next logical statement?","categoryID":322,"dateInserted":"2018-08-11T13:56:17+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-15T08:48:47+00:00","insertUserID":57751,"insertUser":{"userID":57751,"name":"Brad Worth","url":"https:\/\/community.smartsheet.com\/profile\/Brad%20Worth","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-08\/f9\/b9\/nf9b97f6a7777fd5b70784ff4d3cc8ba6.JPG","dateLastActive":"2018-08-11T14:20:26+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":57581,"lastUser":{"userID":57581,"name":"Javed Hassan","url":"https:\/\/community.smartsheet.com\/profile\/Javed%20Hassan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/7EOM552BKGGM\/nBHHPEBSCPTGK.jpg","dateLastActive":"2023-08-14T12:26:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":92,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/30531\/nested-if-then-and-statment","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30531\/nested-if-then-and-statment","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":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":29161,"type":"discussion","name":"cell reference based on date","excerpt":"We have a yearly schedule for all our field guys that is updated a week prior and sent out to the field so everyone knows where they will be working for the upcoming week. My question is can I tie a roll up sheet to the master schedule sheet that I would input a date on the roll up and based on that date grab the matching…","categoryID":322,"dateInserted":"2018-07-24T17:07:12+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-15T08:32:41+00:00","insertUserID":55501,"insertUser":{"userID":55501,"name":"Patrick Eastman","url":"https:\/\/community.smartsheet.com\/profile\/Patrick%20Eastman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-09-04T17:56:26+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":1001,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/29161\/cell-reference-based-on-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/29161\/cell-reference-based-on-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":218,"urlcode":"construction","name":"Construction"},{"tagID":241,"urlcode":"calendar","name":"Calendar View"},{"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":30596,"type":"discussion","name":"Help on formula: countifs with multiple criteria","excerpt":"Hi everyone, I need help on the below formula. I am trying to get the number of active projects based on the columns Section and Stage (see attached image). The 2 sections are called Food and Non-Food. The active stages are called Planning, Strategy Decision, Tender, Evaluation and Contract. Other terms applied in this…","categoryID":322,"dateInserted":"2018-08-14T08:27:57+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-15T01:43:35+00:00","insertUserID":42606,"insertUser":{"userID":42606,"name":"Grace","url":"https:\/\/community.smartsheet.com\/profile\/Grace","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-14T08:25:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":42606,"lastUser":{"userID":42606,"name":"Grace","url":"https:\/\/community.smartsheet.com\/profile\/Grace","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-14T08:25:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":155,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/30596\/help-on-formula-countifs-with-multiple-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30596\/help-on-formula-countifs-with-multiple-criteria","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-08\/8d\/ed\/8ded13f638a04509bdcdd9b5ab377d55.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Countif.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"},{"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":30431,"type":"discussion","name":"Date changes to tomorrow after 5pm","excerpt":"Hello, I have been trying to find a work around for an issue I am having on sheet we use. We are currently using a sheet for employees to submit when they check out and check in equipment via a form. We were using the auto created date column to run reports on what equipment were checked out vs. available. We discovered…","categoryID":322,"dateInserted":"2018-08-09T16:40:28+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-14T12:36:00+00:00","insertUserID":27881,"insertUser":{"userID":27881,"name":"bhope51","url":"https:\/\/community.smartsheet.com\/profile\/bhope51","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-04-04T18:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":27881,"lastUser":{"userID":27881,"name":"bhope51","url":"https:\/\/community.smartsheet.com\/profile\/bhope51","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-04-04T18:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":253,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/30431\/date-changes-to-tomorrow-after-5pm","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30431\/date-changes-to-tomorrow-after-5pm","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-08\/cc\/e8\/cce816be31b4fb094e136c88fd6cd539.PNG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.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":219,"urlcode":"sheets","name":"Sheets"},{"tagID":249,"urlcode":"education","name":"Education"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":263,"urlcode":"government","name":"Government"},{"tagID":281,"urlcode":"support","name":"Support"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"},{"tagID":507,"urlcode":"enhancement-request","name":"Enhancement Request"},{"tagID":549,"urlcode":"account-and-user-management","name":"Account and User 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":30446,"type":"discussion","name":"Multiple Countifs and Children to use in a Dashboard","excerpt":"Hello Everyone! I'm new to figuring out counts in SmartSheets and Dashboads\/Sights and could use a little help. I have a sheet that is connected to Salesforce via the Salesforce Connector, so the data is dynamic. I currently have the sheet grouped by department. Under each department are children rows for projects. There…","categoryID":322,"dateInserted":"2018-08-09T18:13:09+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-14T03:20:57+00:00","insertUserID":57551,"insertUser":{"userID":57551,"name":"mcheung57551","url":"https:\/\/community.smartsheet.com\/profile\/mcheung57551","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!nYFG32VxE3o!7UjEu2lXSKQ!R1U0lNsB31G","dateLastActive":"2020-06-11T16:41:03+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":187,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/30446\/multiple-countifs-and-children-to-use-in-a-dashboard","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30446\/multiple-countifs-and-children-to-use-in-a-dashboard","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":292,"urlcode":"dashboards","name":"Dashboards"}],"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":30516,"type":"discussion","name":"SumIf contains certain text--use FIND?","excerpt":"Sorry, I tried to search the archives but couldn't follow the examples so am hoping someone could help me with this specific scenario. I am trying to put together a budget that is summing up columns that contain certain text. I would like to figure out how to sumif Row 3 all columns containing the term \"grant\" so that I…","categoryID":322,"dateInserted":"2018-08-10T18:30:11+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-13T14:13:54+00:00","insertUserID":57701,"insertUser":{"userID":57701,"name":"Hong Ly","url":"https:\/\/community.smartsheet.com\/profile\/Hong%20Ly","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-13T12:23:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":57701,"lastUser":{"userID":57701,"name":"Hong Ly","url":"https:\/\/community.smartsheet.com\/profile\/Hong%20Ly","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-13T12:23:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":1192,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/30516\/sumif-contains-certain-text-use-find","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30516\/sumif-contains-certain-text-use-find","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-08\/8b\/5e\/8b5efb37db81ea09f890898c7c5cf96b.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screen Shot 2018-08-10 at 3.33.32 PM.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":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":29411,"type":"discussion","name":"formula help","excerpt":"i need help with a formula please. my current formula looks like this.. =SUMIF(Stage:Stage, ???, [Job Value]:[Job Value]) i need an answer to the ??? please as it is a column that has a formula in it that relates to other fields and is a check box. if true then tick the check box. i am now wanting to sum the value of all…","categoryID":322,"dateInserted":"2018-07-26T18:09:41+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-13T07:37:17+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":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":106,"score":null,"hot":9,"url":"https:\/\/community.smartsheet.com\/discussion\/29411\/formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/29411\/formula-help","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":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":30336,"type":"discussion","name":"How to create an auto incrementing number based upon checkbox","excerpt":"Hi everyone, I have a smartsheet where each row represents an \"installation\". One of the columns is called \"Gone Live\" and is of a type checkbox. I have created another column entitled \"Number of Installs this year\". In this column, I want to create a formula that checks the \"Gone Live\" field, and if the checkbox is…","categoryID":322,"dateInserted":"2018-08-08T20:49:46+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-13T02:40:46+00:00","insertUserID":57416,"insertUser":{"userID":57416,"name":"matt.lemsing","url":"https:\/\/community.smartsheet.com\/profile\/matt.lemsing","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-21T21:35:22+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":611,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/30336\/how-to-create-an-auto-incrementing-number-based-upon-checkbox","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30336\/how-to-create-an-auto-incrementing-number-based-upon-checkbox","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":254,"urlcode":"formulas","name":"Formulas"},{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"},{"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":30526,"type":"discussion","name":"SUMIF IF referred sheet\/column ISDATE","excerpt":"Hey everyone, working on something that i am having a problem with I have the following =SUMIF({FSM - Help Desk Ticket Tracker Range 4}, $VlookupValue@row, {FSM - Help Desk Ticket Tracker Range 1}) This returns the value from the cell that i want to perform a specific calculation. I want to add more to this though. The…","categoryID":322,"dateInserted":"2018-08-11T08:58:30+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-12T16:24:54+00:00","insertUserID":57726,"insertUser":{"userID":57726,"name":"CCBigelow","url":"https:\/\/community.smartsheet.com\/profile\/CCBigelow","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-11T08:52:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":4374,"lastUser":{"userID":4374,"name":"J. Craig Williams","title":"","url":"https:\/\/community.smartsheet.com\/profile\/J.%20Craig%20Williams","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/74\/da\/n74da5fe4ff9c5b38c13d6409a3c883a3.jpg","dateLastActive":"2023-05-13T18:07:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":253,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/30526\/sumif-if-referred-sheet-column-isdate","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30526\/sumif-if-referred-sheet-column-isdate","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-08\/1a\/5c\/1a5cacf9221cfa02c35bc76a6da7e616.JPG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Value1.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":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":281,"urlcode":"support","name":"Support"},{"tagID":441,"urlcode":"comments-and-conversations","name":"Comments and Conversations"},{"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":30356,"type":"discussion","name":"Divide by Zero error","excerpt":"Hello, I have the following formula to calculate % Over\/Under Budget: =[$ (Over)\/Under Budget]8 \/ [FOOD BUDGET July 2018]8 I would like this to work even when there is a 0 in the Budget number and even when \"over\/under\" is 0. Thank you for your help. Todd","categoryID":322,"dateInserted":"2018-08-09T08:07:32+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-12T12:19:56+00:00","insertUserID":17811,"insertUser":{"userID":17811,"name":"afineline","url":"https:\/\/community.smartsheet.com\/profile\/afineline","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-09T07:57:51+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":4374,"lastUser":{"userID":4374,"name":"J. Craig Williams","title":"","url":"https:\/\/community.smartsheet.com\/profile\/J.%20Craig%20Williams","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/74\/da\/n74da5fe4ff9c5b38c13d6409a3c883a3.jpg","dateLastActive":"2023-05-13T18:07:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":644,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/30356\/divide-by-zero-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30356\/divide-by-zero-error","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-08\/34\/dc\/34dc64523796b45afb6195daf288e9a0.PNG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Divide by Zero.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":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}]}],"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