Formulas and Functions

=TODAY(-30)\"} Does anyone have any good tips?","categoryID":322,"dateInserted":"2020-12-02T16:01:00+00:00","dateUpdated":null,"dateLastComment":"2020-12-02T16:17:06+00:00","insertUserID":129284,"insertUser":{"userID":129284,"name":"David Rice","url":"https:\/\/community.smartsheet.com\/profile\/David%20Rice","photoUrl":"https:\/\/lh4.googleusercontent.com\/-nQIbGh3yTRs\/AAAAAAAAAAI\/AAAAAAAAAAA\/AMZuuckbZhuSiDGPUH9AGmfRXCu5TBhkGg\/s96-c\/photo.jpg","dateLastActive":"2020-12-18T19:02:17+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":51,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/73613\/having-trouble-with-pulling-data-with-countifs","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73613\/having-trouble-with-pulling-data-with-countifs","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":73581,"type":"question","name":"Calculate total of tiered payment","excerpt":"Hi all, is there a way to break a number down to make multiple calculations? For example, I am going to pay my sales team based on the number of units they sell. The first 50 units are paid at $20\/unit, next 25 units are paid at $30\/unit, etc. In the photo below I want it to automatically calculate the total ($3,250)…","categoryID":322,"dateInserted":"2020-12-01T17:57:31+00:00","dateUpdated":null,"dateLastComment":"2020-12-02T14:32:27+00:00","insertUserID":124230,"insertUser":{"userID":124230,"name":"Brad Mulder","url":"https:\/\/community.smartsheet.com\/profile\/Brad%20Mulder","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-12-08T18:00:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":124230,"lastUser":{"userID":124230,"name":"Brad Mulder","url":"https:\/\/community.smartsheet.com\/profile\/Brad%20Mulder","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-12-08T18:00:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":120,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/73581\/calculate-total-of-tiered-payment","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73581\/calculate-total-of-tiered-payment","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/6IFTJDCXAK3V\/capture3.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture3.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-12-02T14:31:53+00:00","dateAnswered":"2020-12-02T06:02:15+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":73596,"type":"question","name":"How can I make State1 change to Complete when Status is Complete?","excerpt":"When Status1 is Complete; I want State1 to be Complete otherwise stay as Past Due, Due Now, or Upcoming Maintenance.","categoryID":322,"dateInserted":"2020-12-01T22:54:30+00:00","dateUpdated":null,"dateLastComment":"2020-12-02T13:26:36+00:00","insertUserID":42366,"insertUser":{"userID":42366,"name":"Mellba","url":"https:\/\/community.smartsheet.com\/profile\/Mellba","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-03\/13\/ae\/n13ae6cb63b45853af70bf727123f427b.jpg","dateLastActive":"2021-08-31T14:19:34+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":42366,"lastUser":{"userID":42366,"name":"Mellba","url":"https:\/\/community.smartsheet.com\/profile\/Mellba","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-03\/13\/ae\/n13ae6cb63b45853af70bf727123f427b.jpg","dateLastActive":"2021-08-31T14:19:34+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":61,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/73596\/how-can-i-make-state1-change-to-complete-when-status-is-complete","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73596\/how-can-i-make-state1-change-to-complete-when-status-is-complete","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/CBOU5BEFWG5I\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-12-02T13:26:40+00:00","dateAnswered":"2020-12-02T13:26:36+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}]},{"discussionID":73560,"type":"question","name":"Is there a way to make the auto appearing rows disappear on a grid?","excerpt":"I've got a Vlookup 'Search Portal' set up on a grid that I'm viewing through a editable published grid on a Dashboard. It all works pretty well, except for the rows that appear under 'Portal' area. The working area is 19 rows. It adds on 41 blank rows to the bottom which I don't need. Is there a way to try and suppress if…","categoryID":322,"dateInserted":"2020-12-01T11:30:14+00:00","dateUpdated":null,"dateLastComment":"2020-12-02T12:46:52+00:00","insertUserID":113251,"insertUser":{"userID":113251,"name":"Graham Rimell","url":"https:\/\/community.smartsheet.com\/profile\/Graham%20Rimell","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!phHXaR85t_4!pQNXKZdkrro!I0_JXjc_J5c","dateLastActive":"2022-03-16T07:34:37+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":62,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/73560\/is-there-a-way-to-make-the-auto-appearing-rows-disappear-on-a-grid","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73560\/is-there-a-way-to-make-the-auto-appearing-rows-disappear-on-a-grid","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":73603,"type":"question","name":"VLOOKUP ERROR","excerpt":"Hello, I wanted to use VLOOKUP for referencing cells within the same sheet. But it only works for certain range: Range 1: 19 Columns x 3800 Rows -> This work Range 2: 19 Columns x 3900 -> This doesn't work. Gives \"#NO MATCH\" error. Could you please suggest what what to do? Best Regards, Adarsh","categoryID":322,"dateInserted":"2020-12-02T08:59:58+00:00","dateUpdated":null,"dateLastComment":"2020-12-02T09:04:05+00:00","insertUserID":88821,"insertUser":{"userID":88821,"name":"ahumne","url":"https:\/\/community.smartsheet.com\/profile\/ahumne","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-12-17T15:51:13+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":1,"countViews":51,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/73603\/vlookup-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73603\/vlookup-error","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":73588,"type":"question","name":"How to create a formula to flag at risk events","excerpt":"Hello Community, I am trying to create a formula that will create a flag if the Status of Resolution' is selected as \"Unresolved\". Is this possible in Smartsheet, and if so, any guidance on the formula to use to generate the flag would be very appreciated.","categoryID":322,"dateInserted":"2020-12-01T19:33:55+00:00","dateUpdated":null,"dateLastComment":"2020-12-01T20:00:13+00:00","insertUserID":121545,"insertUser":{"userID":121545,"name":"Tamiko Smith","url":"https:\/\/community.smartsheet.com\/profile\/Tamiko%20Smith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-03-23T20:01:20+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":51,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/73588\/how-to-create-a-formula-to-flag-at-risk-events","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73588\/how-to-create-a-formula-to-flag-at-risk-events","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/UPMTLOOB0P31\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-12-01T19:51:35+00:00","dateAnswered":"2020-12-01T19:48:18+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}]},{"discussionID":73576,"type":"question","name":"Formula to derive fiscal quarter from a date","excerpt":"I've been working on a formula to try to derive a fiscal quarter from a date value. Depending upon the data value, I want the output to be in the follow format: Working previously with a Smartsheet help agent, I've added a couple of helper columns but I'm not yet getting it to work. The columns I'm work with are: Target…","categoryID":322,"dateInserted":"2020-12-01T16:21:06+00:00","dateUpdated":null,"dateLastComment":"2020-12-01T19:47:42+00:00","insertUserID":125759,"insertUser":{"userID":125759,"name":"Guaca Mohle","url":"https:\/\/community.smartsheet.com\/profile\/Guaca%20Mohle","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zxPeMz7Hwf4!3VDfv86wi5Y!xMof-OLUf15","dateLastActive":"2021-03-17T15:15:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":670,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/73576\/formula-to-derive-fiscal-quarter-from-a-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73576\/formula-to-derive-fiscal-quarter-from-a-date","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/GVBDJ7RVFUMM\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-12-01T19:21:15+00:00","dateAnswered":"2020-12-01T19:06:27+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":73587,"type":"question","name":"Pulling specific modifications dates from the past","excerpt":"Hi! I am tracking documents that need to be signed. I have a status column that tracks if it's \"signed\", \"unsigned\", or \"refused\". I'm needing to go back and collect the dates that they were turned to \"refused\". Too much time has gone by, and so the modification date field doesn't work. Looking at each cell history would…","categoryID":322,"dateInserted":"2020-12-01T19:00:07+00:00","dateUpdated":"2020-12-01T19:00:57+00:00","dateLastComment":"2020-12-01T19:31:57+00:00","insertUserID":126705,"insertUser":{"userID":126705,"name":"Kelsey Ciarrocca","url":"https:\/\/community.smartsheet.com\/profile\/Kelsey%20Ciarrocca","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-10T19:54:38+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":126705,"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":2,"countViews":62,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/73587\/pulling-specific-modifications-dates-from-the-past","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/73587\/pulling-specific-modifications-dates-from-the-past","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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Help Article Resources