Formulas and Functions

0, \"P\", \"A\") Exhausting my resources before dictating how time is entered. Thank you in advance Sharon C.","categoryID":322,"dateInserted":"2020-07-01T17:02:15+00:00","dateUpdated":null,"dateLastComment":"2020-07-02T14:58:52+00:00","insertUserID":117744,"insertUser":{"userID":117744,"name":"Sharon Castiglia","url":"https:\/\/community.smartsheet.com\/profile\/Sharon%20Castiglia","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-07-15T22:08: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-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":365,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/69545\/find-function-case-sensitive","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69545\/find-function-case-sensitive","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-07-02T14:48:36+00:00","dateAnswered":"2020-07-02T14:38:59+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":69566,"type":"question","name":"Date Column Automation","excerpt":"Is there a way to automate those columns so they can recognize a pattern? For example. If I create a task that happens every two weeks and schedule the first set of dates, is there a way to automate that, so if I pull the cells down, it can automatically calculate the next set of dates?","categoryID":322,"dateInserted":"2020-07-02T12:18:40+00:00","dateUpdated":null,"dateLastComment":"2020-07-02T13:44:57+00:00","insertUserID":122506,"insertUser":{"userID":122506,"name":"Krista Cornew","url":"https:\/\/community.smartsheet.com\/profile\/Krista%20Cornew","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GjLr2VUcybWyiek1SF-mbtktitF1ugwFF1UTIoQ","dateLastActive":"2021-08-02T17:31:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":21226,"lastUser":{"userID":21226,"name":"Jason Tarpinian","url":"https:\/\/community.smartsheet.com\/profile\/Jason%20Tarpinian","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-25T21:44:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":72,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/69566\/date-column-automation","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69566\/date-column-automation","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":69542,"type":"question","name":"Rolling 12-Month Grid","excerpt":"I've built the rolling 12-month grid below, which will be referenced elsewhere in this summary sheet.It works, but this could be cleaner. I know I could remove the \"helper\" row (row 1), but this is how I worked it out, so I kept it for now. Any suggestions for improvements would be appreciated. In m1 row 2, I have this…","categoryID":322,"dateInserted":"2020-07-01T15:31:36+00:00","dateUpdated":"2020-07-01T15:32:31+00:00","dateLastComment":"2020-07-02T13:43:03+00:00","insertUserID":115071,"insertUser":{"userID":115071,"name":"Marvin Daniels","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Marvin%20Daniels","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BWnXAZtmKjw!!zIOgWnCgsaD","dateLastActive":"2023-08-24T20:49:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":115071,"lastUserID":115071,"lastUser":{"userID":115071,"name":"Marvin Daniels","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Marvin%20Daniels","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BWnXAZtmKjw!!zIOgWnCgsaD","dateLastActive":"2023-08-24T20:49:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":250,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/69542\/rolling-12-month-grid","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69542\/rolling-12-month-grid","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/HDO886VAGRU8\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-07-02T12:56:56+00:00","dateAnswered":"2020-07-01T19:36:26+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":69567,"type":"question","name":"COUNTIFS per calendar month","excerpt":"This discussion was created from comments split from: Count of cells in a date range.","categoryID":322,"dateInserted":"2020-07-02T12:52:42+00:00","dateUpdated":null,"dateLastComment":"2020-07-02T12:58:39+00:00","insertUserID":113652,"insertUser":{"userID":113652,"name":"System","title":"","url":"https:\/\/community.smartsheet.com\/profile\/System","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/650\/n67RVONBT7K0D.png","dateLastActive":"2023-08-24T15:55:34+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"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":104,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/69567\/countifs-per-calendar-month","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69567\/countifs-per-calendar-month","format":"Html","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":69526,"type":"question","name":"Not so pretty IF-function (works) but needs makeover","excerpt":"Keeping track on vacation time I need to convert dates to weeks and then again to an overview with checkboxes. In order to get a list of weeks [c_weeks_listed] (later in use with FIND) I have come up with the following IF-formula which works but will become too complex if expanded further. Any ideas for a more powerful…","categoryID":322,"dateInserted":"2020-06-30T21:21:01+00:00","dateUpdated":null,"dateLastComment":"2020-07-02T12:13:25+00:00","insertUserID":119767,"insertUser":{"userID":119767,"name":"HOFF","title":"","url":"https:\/\/community.smartsheet.com\/profile\/HOFF","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-06-30T21:09:21+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":63,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/69526\/not-so-pretty-if-function-works-but-needs-makeover","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69526\/not-so-pretty-if-function-works-but-needs-makeover","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/KY7UNNOKHU19\/vacation-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"vacation.JPG"},"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":210,"urlcode":"human-resources","name":"Human Resources"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":265,"urlcode":"reports","name":"Reports"}],"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":69541,"type":"question","name":"Counting Criteria that Overlaps","excerpt":"I have a multi select dropdown column where users can select phases of a job they have experience in. For this example, let's say they have the options of \"Pre-Execution\", \"Execution\", and \"Closeout\". I want to total the number of users with \"Execution\" experience. Using \"=COUNTIF([dropdown]:[dropdown],…","categoryID":322,"dateInserted":"2020-07-01T15:29:41+00:00","dateUpdated":"2020-07-01T15:30:24+00:00","dateLastComment":"2020-07-02T11:52:08+00:00","insertUserID":123203,"insertUser":{"userID":123203,"name":"Alan Donovan","url":"https:\/\/community.smartsheet.com\/profile\/Alan%20Donovan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-07-09T14:51:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":123203,"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":11,"countViews":75,"score":null,"hot":11,"url":"https:\/\/community.smartsheet.com\/discussion\/69541\/counting-criteria-that-overlaps","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69541\/counting-criteria-that-overlaps","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-07-01T22:27:24+00:00","dateAnswered":"2020-07-01T22:27:01+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":69548,"type":"question","name":"IF(AND's and IF(OR's","excerpt":"I have the below formula working to assign a price pursuant to particular job's complexity. =IF(COMPLEXITY517 = \"LOW\", 1000, IF(COMPLEXITY517 = \"MID\", 1500, IF(COMPLEXITY517 = \"HIGH\", 2000, IF(COMPLEXITY517 = \"HQP\", 2500)))) However, I have 2 tiers of customer pricing that i'd like to track. The above covers +90% of the…","categoryID":322,"dateInserted":"2020-07-01T18:54:11+00:00","dateUpdated":null,"dateLastComment":"2020-07-02T11:38:05+00:00","insertUserID":118708,"insertUser":{"userID":118708,"name":"Damon Lloyd","url":"https:\/\/community.smartsheet.com\/profile\/Damon%20Lloyd","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!c7tcJ5Bwx3w!ptldKbBxvbw!YAVEsTduD8H","dateLastActive":"2022-08-29T15:09:30+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":63,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/69548\/if-ands-and-if-ors","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69548\/if-ands-and-if-ors","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-07-02T12:58:40+00:00","dateAnswered":"2020-07-01T19:14:55+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":69501,"type":"discussion","name":"Help with iferror","excerpt":"Hi, I have this formula that i have written. It works fine, but I am getting blank fields, when I was expecting N\/A. The product line row has EBS, JDE, SAP. The TLR Scope is Financials, HR, Payroll For EBS I want values returned and N\/A for the other product lines. =IFERROR(IF(AND($[Product Line]@row = \"EBS\", $[TLR…","categoryID":322,"dateInserted":"2020-06-30T00:56:54+00:00","dateUpdated":null,"dateLastComment":"2020-07-02T10:50:58+00:00","insertUserID":35911,"insertUser":{"userID":35911,"name":"Peggy","url":"https:\/\/community.smartsheet.com\/profile\/Peggy","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7Htee2AaTfQ!htPdobx0PT4!J2mAMiaMDmW","dateLastActive":"2023-08-09T17:57:52+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-25T20:36:29+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":62,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/69501\/help-with-iferror","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/69501\/help-with-iferror","format":"Rich","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":[],"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