Formulas

= [Completion Due Date]29, [Days to Completion]29 < 100), \"BEHIND\", \"\")","categoryID":322,"dateInserted":"2019-01-09T16:27:49+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-01-10T15:36:47+00:00","insertUserID":74666,"insertUser":{"userID":74666,"name":"RotorYacht","url":"https:\/\/community.smartsheet.com\/profile\/RotorYacht","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-01\/f4\/dc\/nf4dc30791a516ac4809f582bb5069d1c.jpg","dateLastActive":"2019-01-09T16:19:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":11072,"lastUser":{"userID":11072,"name":"Mike Wilday","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Wilday","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/749H48MC3045\/nF9FQYPTX0X7D.jpg","dateLastActive":"2023-08-23T14:56:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":63,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/38686\/formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/38686\/formulas","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-01\/95\/5a\/955a8859a73b019cb982060659f44753.JPG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"IMG_3298.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":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":38561,"type":"discussion","name":"Calculating the duration between 4 dates","excerpt":"Does anyone know the formula to count the days between 4 dates. If the Ready to Launch date is greater than or equal to the Received IO, then the Duration would equal the number of days from Start to Launched. Otherwise, the Duration would equal (days from Start to Ready to Launch) + (days from Received IO to Launch).…","categoryID":322,"dateInserted":"2019-01-08T14:45:41+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-01-10T09:43:31+00:00","insertUserID":74456,"insertUser":{"userID":74456,"name":"Jericho","url":"https:\/\/community.smartsheet.com\/profile\/Jericho","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-01-08T14:26:41+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":106,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/38561\/calculating-the-duration-between-4-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/38561\/calculating-the-duration-between-4-dates","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-01\/4c\/d8\/4cd861bc689ce1c7faed35b04673d358.JPG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Smartsheet Calc.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":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":38696,"type":"discussion","name":"Index If?","excerpt":"Hi. I'm trying to retrieve a cell value from one sheet to another where it looks up the ID# and retrieves the Score. However, I want it to retrieve the most recent Score, because the sheet it is retrieving from is a historical record with many different scores assigned to that ID# over time. Could someone help me with the…","categoryID":322,"dateInserted":"2019-01-09T18:13:56+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-01-10T08:33:21+00:00","insertUserID":74716,"insertUser":{"userID":74716,"name":"DBody","url":"https:\/\/community.smartsheet.com\/profile\/DBody","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-01-09T18:09:41+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":2,"countViews":52,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/38696\/index-if","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/38696\/index-if","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"}],"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":37576,"type":"discussion","name":"Formula help - trying to sum a column if launch date is in the next 30 days","excerpt":"Hello, I'm trying to sum numbers in a column a if the launch date is within the next 30 days. Thanks in advance for any help!","categoryID":322,"dateInserted":"2018-12-15T12:53:15+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-01-10T08:11:15+00:00","insertUserID":67151,"insertUser":{"userID":67151,"name":"MWester","url":"https:\/\/community.smartsheet.com\/profile\/MWester","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-10-03T18:07:14+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":3,"countViews":440,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/37576\/formula-help-trying-to-sum-a-column-if-launch-date-is-in-the-next-30-days","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/37576\/formula-help-trying-to-sum-a-column-if-launch-date-is-in-the-next-30-days","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":207,"urlcode":"sales","name":"Sales"},{"tagID":234,"urlcode":"health-care","name":"Healthcare"},{"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":6337,"type":"discussion","name":"Reference a Cell in another Sheet in a Formula?","excerpt":"Howdy! I'd like to count the number of occurances of a specific value in a column on another sheet. It works great this way, but within the same sheet: =COUNTIF([Column4]17:[Column4]26, \"Red\") Is there a way to select a range of cells on another sheet and return (in this case) the number of them that are Red? Thanks!","categoryID":318,"dateInserted":"2016-10-19T16:09:29+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-01-10T05:25:10+00:00","insertUserID":8763,"insertUser":{"userID":8763,"name":"Bill Leonard","url":"https:\/\/community.smartsheet.com\/profile\/Bill%20Leonard","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2016-10-19T18:16:25+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":68906,"lastUser":{"userID":68906,"name":"SRenner","url":"https:\/\/community.smartsheet.com\/profile\/SRenner","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7I1X-8GKSmQ!b89UW0XZPHo!VA1YDbgtSqD","dateLastActive":"2021-07-15T17:57:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":true,"sink":false,"countComments":4,"countViews":589,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/6337\/reference-a-cell-in-another-sheet-in-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/6337\/reference-a-cell-in-another-sheet-in-a-formula","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":318,"name":"Archived 2016 Posts","url":"https:\/\/community.smartsheet.com\/categories\/c2016","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"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":38676,"type":"discussion","name":"Using CHILDREN() to Define Range in VLOOKUP()","excerpt":"I have a parent row on Row 1 with three indented child rows. I want to populate a cell in the parent row with the first name from a child row that matches a value in one of the columns. There will be other parent rows with their own children, and the number of children may vary. Each parent row represents a \"family\", and…","categoryID":322,"dateInserted":"2019-01-09T14:44:17+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-01-09T15:34:10+00:00","insertUserID":74646,"insertUser":{"userID":74646,"name":"jjShnabadu","url":"https:\/\/community.smartsheet.com\/profile\/jjShnabadu","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-01-09T14:16:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":74646,"lastUser":{"userID":74646,"name":"jjShnabadu","url":"https:\/\/community.smartsheet.com\/profile\/jjShnabadu","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-01-09T14:16:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":606,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/38676\/using-children-to-define-range-in-vlookup","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/38676\/using-children-to-define-range-in-vlookup","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-01\/a5\/63\/a563d379fd36ed2c666aed2719e75927.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"VLOOKUP_ERROR_01.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"}],"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":38556,"type":"discussion","name":"Formula instead of cell linking?","excerpt":"Hi all, please note I've never used any functions like this so not sure if I'm asking in the right way. I have two sheets: Sheet 1 includes the following columns: - Project # - Start Date Sheet 2 includes the following columns: - Project # - Start Date (blank, needs to be filled with formula) How do I build a formula in…","categoryID":322,"dateInserted":"2019-01-08T13:52:08+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-01-09T15:29:14+00:00","insertUserID":2723,"insertUser":{"userID":2723,"name":"Jaye Casciano","url":"https:\/\/community.smartsheet.com\/profile\/Jaye%20Casciano","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/OB4IJYEZR1XW\/nU83D7QKKE5D0.jpg","dateLastActive":"2023-08-24T20:35:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":2723,"lastUser":{"userID":2723,"name":"Jaye Casciano","url":"https:\/\/community.smartsheet.com\/profile\/Jaye%20Casciano","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/OB4IJYEZR1XW\/nU83D7QKKE5D0.jpg","dateLastActive":"2023-08-24T20:35:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":12,"countViews":1037,"score":null,"hot":12,"url":"https:\/\/community.smartsheet.com\/discussion\/38556\/formula-instead-of-cell-linking","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/38556\/formula-instead-of-cell-linking","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":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":38656,"type":"discussion","name":"How to keep a formula when a cell is modified","excerpt":"Hello, I have a bit of an odd situation and I'm hoping that someone might know how to help. We have a Smartsheet that we use when new clients come on board so that our Customer Care team can complete the account setup. The new clients are added to the Smartsheet via a form and we have formulas to automatically add \"N\/A\" to…","categoryID":322,"dateInserted":"2019-01-09T11:38:54+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-01-09T14:04:50+00:00","insertUserID":74576,"insertUser":{"userID":74576,"name":"Tiffany White","url":"https:\/\/community.smartsheet.com\/profile\/Tiffany%20White","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-04-30T13:20:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":74576,"lastUser":{"userID":74576,"name":"Tiffany White","url":"https:\/\/community.smartsheet.com\/profile\/Tiffany%20White","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-04-30T13:20:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":85,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/38656\/how-to-keep-a-formula-when-a-cell-is-modified","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/38656\/how-to-keep-a-formula-when-a-cell-is-modified","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":281,"urlcode":"support","name":"Support"}],"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}">