Formulas and Functions

s in the OR function. Using the formula below I receive one answer: =(COUNTIFS({TACTIC actual delivery date}, >=(DATE(2019, 1, 1)), {TACTIC actual delivery date}, <=(TODAY()), @{TACTIC tactic status}, OR(@cell <> \"Cancelled\", @cell <> \"On hold\"))) While editing the formula to split…","categoryID":322,"dateInserted":"2019-06-03T15:17:54+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-06-04T08:12:41+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":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\/49696\/can-anyone-spot-an-error-in-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/49696\/can-anyone-spot-an-error-in-this-formula","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":49646,"type":"discussion","name":"Locked rows not picked up by automations","excerpt":"Hi all, I have a spreadsheet with just 4 columns, and i have a set of rules to apply to them. One of those rules is sending Update requests if the date column is over 7days in the past. But, since the spreadsheet will have \"countless\" rows, i want the rows that had Update requests before, to be locked (easy part) after the…","categoryID":322,"dateInserted":"2019-06-03T09:48:59+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-06-03T16:07:29+00:00","insertUserID":53051,"insertUser":{"userID":53051,"name":"Jose da Silva","url":"https:\/\/community.smartsheet.com\/profile\/Jose%20da%20Silva","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-02-12T08:13:54+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":122,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/49646\/locked-rows-not-picked-up-by-automations","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/49646\/locked-rows-not-picked-up-by-automations","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":204,"urlcode":"forms","name":"Forms"},{"tagID":207,"urlcode":"sales","name":"Sales"},{"tagID":211,"urlcode":"manufacturing","name":"Manufacturing"},{"tagID":234,"urlcode":"health-care","name":"Healthcare"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":510,"urlcode":"email-notifications","name":"Email Notifications"}],"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":49346,"type":"discussion","name":"Removing System Generated column (Date) contents","excerpt":"I have completed building and testing my sheet and Dashboards. I now want to remove the System Generated Date for all my Form entries used for testing. If I remove the column and reinsert it, the formulas dont work. Is there a simple way of removing the system generated date data?","categoryID":322,"dateInserted":"2019-05-29T20:39:46+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-06-03T13:59:17+00:00","insertUserID":90396,"insertUser":{"userID":90396,"name":"wrightco12","url":"https:\/\/community.smartsheet.com\/profile\/wrightco12","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-06-03T12:50:00+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":223,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/49346\/removing-system-generated-column-date-contents","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/49346\/removing-system-generated-column-date-contents","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":219,"urlcode":"sheets","name":"Sheets"},{"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":49476,"type":"discussion","name":"Formulas","excerpt":"Hi, I have a formula that works perfectly in Excel but I can not get it to work in Smartsheets. I am having issues finding corresponding functions in Smartsheets. This is the formula and screenshots show what I am getting from them.…","categoryID":322,"dateInserted":"2019-05-30T17:44:54+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-06-03T12:27:39+00:00","insertUserID":90481,"insertUser":{"userID":90481,"name":"eganji","url":"https:\/\/community.smartsheet.com\/profile\/eganji","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-09-28T16:47:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":90481,"lastUser":{"userID":90481,"name":"eganji","url":"https:\/\/community.smartsheet.com\/profile\/eganji","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-09-28T16:47:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":291,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/49476\/formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/49476\/formulas","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-05\/2d\/b3\/2db3cf0db997f1a2a43a30a6a094c4dc.PNG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screen Shot 05-30-19 at 02.31 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":234,"urlcode":"health-care","name":"Healthcare"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":263,"urlcode":"government","name":"Government"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"}],"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":49556,"type":"discussion","name":"SUMIFS Statement under Parent Item","excerpt":"I need to Sum the \"Total\" column cells IF: 1) Column Department Allocation Code = \"49 CONTINUOUS IMPROVEMENT: Formula: =SUMIFS(Total:Total, Department Allocation Code:Department Allocation Code, =\"49 CONTINUOUS IMPROVEMENT\", Item:Item, =PARENT(Item1)) *Not working TODAY(); [Start (real)]4 = \"//www.santa-greenland.com/community/categories/formulas-and-functions/\"; \"Green\")))) It is returning \"#INVALID DATA TYPE\". The \"Start (real)\" and \"Start…","categoryID":322,"dateInserted":"2019-06-01T18:30:12+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-06-03T10:26:00+00:00","insertUserID":90711,"insertUser":{"userID":90711,"name":"Julieta Dienstmann","url":"https:\/\/community.smartsheet.com\/profile\/Julieta%20Dienstmann","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-06\/a4\/13\/na413a772e87e99d8999f6383b4d858bd.png","dateLastActive":"2019-06-17T17:50: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":15,"countViews":229,"score":null,"hot":15,"url":"https:\/\/community.smartsheet.com\/discussion\/49591\/if-and-returning-invalid-data-type","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/49591\/if-and-returning-invalid-data-type","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":49631,"type":"discussion","name":"Cross Sheet referencing","excerpt":"Hello, I have a master sheet, where I am keeping track of the progress of various tasks. The progress is being monitored using checkboxes. The structure of the sheet will be clearer through IMAGE A. Each project has it's own smartsheet and I would like to format the master sheet based on targets met in the individual…","categoryID":322,"dateInserted":"2019-06-03T02:39:29+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-06-03T10:02:46+00:00","insertUserID":64576,"insertUser":{"userID":64576,"name":"C. Srinidhi Ragunathan","url":"https:\/\/community.smartsheet.com\/profile\/C.%20Srinidhi%20Ragunathan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-02-13T07:25:56+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":62,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/49631\/cross-sheet-referencing","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/49631\/cross-sheet-referencing","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-06\/01\/b1\/01b19cd88da326084921c905bce39e12.PNG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"IMAGE A.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":49616,"type":"discussion","name":"WEEKDAY formula is giving me the wrong result","excerpt":"Ok, am I missing something obvious or is the formula not working properly? As shown in the image, the date is 6\/2. Which is a Sunday. But when I use the \"WEEKDAY\" formula to reference the date column it keeps coming back with \"2,\" indicating that it's Monday. What am I doing wrong?","categoryID":322,"dateInserted":"2019-06-02T21:27:06+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-06-03T09:59:25+00:00","insertUserID":10544,"insertUser":{"userID":10544,"name":"Tamara K","url":"https:\/\/community.smartsheet.com\/profile\/Tamara%20K","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/d6\/6e\/nd66e8fa5f6bb6e2dcbbb52f06f49cd6d.jpg","dateLastActive":"2019-12-04T18:42: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-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":245,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/49616\/weekday-formula-is-giving-me-the-wrong-result","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/49616\/weekday-formula-is-giving-me-the-wrong-result","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-06\/1c\/cb\/1ccb9e4cdc020bc00456d415c4aa8e4e.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Day2.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":49451,"type":"discussion","name":"#Invalid Operation","excerpt":"Hello, I have this formula: =IF(AND([Completion Date]@row = \"//www.santa-greenland.com/community/categories/formulas-and-functions/\", [Date Range End]@row < TODAY()), \"Past Due\", \"ALL DONE\") I've tried a few things but I keep getting invalid operation, and I'm sure it's something foolish and easy but I can't seem to figure it out. Tahnk you- ","categoryID":322,"dateInserted":"2019-05-30T15:02:47+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-06-03T08:24:48+00:00","insertUserID":40981,"insertUser":{"userID":40981,"name":"Lisi","url":"https:\/\/community.smartsheet.com\/profile\/Lisi","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-10-07T11:34:42+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":6,"countViews":438,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/49451\/invalid-operation","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/49451\/invalid-operation","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":344,"urlcode":"it-operations","name":"IT & Operations"}],"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