Formulas and Functions

*Completed*\", {PMO Support Tasks Range 1},…","categoryID":322,"dateInserted":"2020-08-05T14:48:17+00:00","dateUpdated":null,"dateLastComment":"2020-08-05T15:36:40+00:00","insertUserID":121492,"insertUser":{"userID":121492,"name":"Kelly Gabel","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Gabel","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!kjnW_5tueLw!xPdXcVM75rw!qrOrVgQk8SR","dateLastActive":"2023-03-29T20:30:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-20T03:23:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":62,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/70375\/i-need-help-with-my-countifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70375\/i-need-help-with-my-countifs-formula","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}]},{"discussionID":70373,"type":"question","name":"Calculate number of days a task is later than forecast end date","excerpt":"Hi, Can somebody help? I need to add a column that calculates how many days late tasks less than 100% complete are compared to today see below example, basically need a formula that calculates the 'working days late' column. Thanks forecast end date % Complete working days late (based on todays date) 3\/8\/20 99% 2","categoryID":322,"dateInserted":"2020-08-05T14:00:49+00:00","dateUpdated":null,"dateLastComment":"2020-08-05T15:13:37+00:00","insertUserID":116748,"insertUser":{"userID":116748,"name":"Dan Harris","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Dan%20Harris","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-02-21T09:55:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":124372,"lastUser":{"userID":124372,"name":"Mathieu PERSICO","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mathieu%20PERSICO","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/JL9RVWFGDJGG\/nEXC7GUEA748S.jpg","dateLastActive":"2022-10-12T17:19:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":81,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/70373\/calculate-number-of-days-a-task-is-later-than-forecast-end-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70373\/calculate-number-of-days-a-task-is-later-than-forecast-end-date","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"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":70356,"type":"question","name":"Return a sum using using sumifs and dates","excerpt":"My objective is to return a sum of all 'Work Hours' that are in a department (Wire in this example), that fall within a date range of the 'END' date that Smartsheet is reporting.. This formula returns a value, but I've checked manually, and I've determined the value is incorrect. I've exported a filtered view of the sheet,…","categoryID":322,"dateInserted":"2020-08-04T17:09:15+00:00","dateUpdated":null,"dateLastComment":"2020-08-05T15:12:37+00:00","insertUserID":120384,"insertUser":{"userID":120384,"name":"Larry Bejcar","url":"https:\/\/community.smartsheet.com\/profile\/Larry%20Bejcar","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-08-21T20:40:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-19T18:57:54+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":73,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/70356\/return-a-sum-using-using-sumifs-and-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70356\/return-a-sum-using-using-sumifs-and-dates","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-05T15:20:02+00:00","dateAnswered":"2020-08-05T15:09:24+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":211,"urlcode":"Manufacturing","name":"Manufacturing"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":319,"urlcode":"functionality","name":"functionality"},{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}],"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":70369,"type":"question","name":"COUNTIFS Issue","excerpt":"Hi, I am trying to count between a range of numbers. The problem is smartsheet seems to only count the first number and everything after that. It doesnt seem to count between the range. Range to count is Probability - This has numbers ranging between 1 and 0.50 I have tried the following =COUNTIFS({Master Programme Range…","categoryID":322,"dateInserted":"2020-08-05T08:39:26+00:00","dateUpdated":null,"dateLastComment":"2020-08-05T15:09:05+00:00","insertUserID":124838,"insertUser":{"userID":124838,"name":"Phil2020","url":"https:\/\/community.smartsheet.com\/profile\/Phil2020","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-08-10T09:16:01+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-20T03:23:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":72,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/70369\/countifs-issue","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70369\/countifs-issue","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-05T14:49:56+00:00","dateAnswered":"2020-08-05T14:38:31+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":70361,"type":"question","name":"Nested IF Statement Help Needed","excerpt":"Hi there, Here is what I am trying to achieve: If the % Complete column =100%, then return Gray to the status If a task is overdue (using the End Date column), return Red to the status If a task is due within 5 days, return Yellow to the status Is a task is due in 6 or more days, return Green to the status If there is no…","categoryID":322,"dateInserted":"2020-08-04T20:42:07+00:00","dateUpdated":null,"dateLastComment":"2020-08-05T14:22:05+00:00","insertUserID":124830,"insertUser":{"userID":124830,"name":"Andrea Whittaker","url":"https:\/\/community.smartsheet.com\/profile\/Andrea%20Whittaker","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ny9VnyNtg3Q!UwFV3zNDf3w!y_H0WbMZqte","dateLastActive":"2021-03-23T14:00:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-20T03:23:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":83,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/70361\/nested-if-statement-help-needed","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70361\/nested-if-statement-help-needed","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/WWKIOAWISQ0G\/ss-image-help.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"SS Image help.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-05T13:57:47+00:00","dateAnswered":"2020-08-05T11:46:16+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":69785,"type":"question","name":"today() automatically change or not","excerpt":"Hi, confused for using Today() formula in my sheet.. Currently using countif and sumif formula based on today() date. please confirm that formula automatically changed when date today() change or when I can manually activate my sheet first and then formula changed.","categoryID":322,"dateInserted":"2020-07-13T05:41:10+00:00","dateUpdated":null,"dateLastComment":"2020-08-05T13:43:42+00:00","insertUserID":119414,"insertUser":{"userID":119414,"name":"Yashwant Singh","url":"https:\/\/community.smartsheet.com\/profile\/Yashwant%20Singh","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xUHUsZ9hKP4!hRNW4WMc77Q!UkcZ7X7Sevw","dateLastActive":"2021-10-11T08:51:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":35331,"lastUser":{"userID":35331,"name":"Andrea Vecchi","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andrea%20Vecchi","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/678\/nTJS3BCKH4KQT.jpg","dateLastActive":"2023-07-19T13:05:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":17,"countViews":1327,"score":null,"hot":17,"url":"https:\/\/community.smartsheet.com\/discussion\/69785\/today-automatically-change-or-not","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69785\/today-automatically-change-or-not","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-07-14T11:38:19+00:00","dateAnswered":"2020-07-13T13:09:16+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":68621,"type":"question","name":"Project look ahead","excerpt":"Hi, I am looking to add a widget to a dashboard to show a two week look ahead of a project. I have a project sheet and essentially want to create a formula to check a box, from which I can use to trigger a report.","categoryID":322,"dateInserted":"2020-05-26T20:55:35+00:00","dateUpdated":null,"dateLastComment":"2020-08-05T12:50:10+00:00","insertUserID":119154,"insertUser":{"userID":119154,"name":"John Kelly","url":"https:\/\/community.smartsheet.com\/profile\/John%20Kelly","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3HjUP0iCgpQ!oStWaBcbkro!f6nFgg3GgdQ","dateLastActive":"2023-08-08T20:47:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":124308,"lastUser":{"userID":124308,"name":"Jace Jontz","url":"https:\/\/community.smartsheet.com\/profile\/Jace%20Jontz","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-08-05T12:49:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":1159,"score":null,"hot":8,"url":"https:\/\/community.smartsheet.com\/discussion\/68621\/project-look-ahead","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/68621\/project-look-ahead","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-05-27T21:18:41+00:00","dateAnswered":"2020-05-27T12:41:08+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":70360,"type":"question","name":"How to uncheck a row field if another field in the row is set to a specific value","excerpt":"What I have so far: The box gets checked (true, 1) when a target due date is within 60 days of today. My current formula is: =IF([Target Due Date]3 - 60 < TODAY(), 1) What I hope to achieve: Find a way to uncheck (false, 0) the same box when Status is set to Closed. I just can't seem to come up with anything that works for…","categoryID":322,"dateInserted":"2020-08-04T20:35:55+00:00","dateUpdated":"2020-08-04T20:36:22+00:00","dateLastComment":"2020-08-05T12:42:42+00:00","insertUserID":124228,"insertUser":{"userID":124228,"name":"Nikola Djukic","url":"https:\/\/community.smartsheet.com\/profile\/Nikola%20Djukic","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-08-05T14:34:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":124228,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-20T03:23:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":72,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/70360\/how-to-uncheck-a-row-field-if-another-field-in-the-row-is-set-to-a-specific-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70360\/how-to-uncheck-a-row-field-if-another-field-in-the-row-is-set-to-a-specific-value","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/7O4PYBZXX2PY\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-05T12:35:24+00:00","dateAnswered":"2020-08-05T11:47:09+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