Formulas and Functions

\"Complete\", Status4 <> \"Sent\/Complete\"), \"not complete\", \"Complete\") which always returns \"not complete\", i.e. true. However, modifying the expression so that only 1 logical expression is present, the expression works correctly, showing the true and false outcomes as…","categoryID":322,"dateInserted":"2020-04-10T19:36:32+00:00","dateUpdated":"2020-04-10T19:44:10+00:00","dateLastComment":"2020-04-15T20:34:17+00:00","insertUserID":120428,"insertUser":{"userID":120428,"name":"Fiona Painter","url":"https:\/\/community.smartsheet.com\/profile\/Fiona%20Painter","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xrjWcS8b_CY!BRFUAbSGL3o!S5PxhFwkKq5","dateLastActive":"2020-05-04T19:29:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":120428,"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-07-23T22:26:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":175,"score":null,"hot":9,"url":"https:\/\/community.smartsheet.com\/discussion\/67347\/or-function-not-working-with-multiple-logical-expressions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/67347\/or-function-not-working-with-multiple-logical-expressions","format":"rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-04-15T20:30:31+00:00","dateAnswered":"2020-04-13T19:47:49+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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":281,"urlcode":"Support","name":"Support"},{"tagID":319,"urlcode":"functionality","name":"functionality"},{"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":67443,"type":"question","name":"how to realize a projectplan with planned start\/end\/effort\/duration vs. actual start\/end\/effort\/....","excerpt":"Hi all, we are successfull planning projects with smartsheet. Now we would like to compare planned start\/end\/effort\/duration vs. actual start\/end\/effort\/duration. Any idea how to do that? Baseplans? How to visualice two gantt bars planned vs. actual ( vs. base x plan) Thank you and best wishes, Joachim","categoryID":322,"dateInserted":"2020-04-15T11:48:22+00:00","dateUpdated":null,"dateLastComment":"2020-04-15T19:10:09+00:00","insertUserID":41501,"insertUser":{"userID":41501,"name":"Joachim Mund","url":"https:\/\/community.smartsheet.com\/profile\/Joachim%20Mund","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5zNCeZKbYDo!n8PAH775fLw!O6MnWziB8Y_","dateLastActive":"2023-04-02T14:21:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":41501,"lastUser":{"userID":41501,"name":"Joachim Mund","url":"https:\/\/community.smartsheet.com\/profile\/Joachim%20Mund","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5zNCeZKbYDo!n8PAH775fLw!O6MnWziB8Y_","dateLastActive":"2023-04-02T14:21:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":111,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/67443\/how-to-realize-a-projectplan-with-planned-start-end-effort-duration-vs-actual-start-end-effort","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/67443\/how-to-realize-a-projectplan-with-planned-start-end-effort-duration-vs-actual-start-end-effort","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":67430,"type":"question","name":"Date Formula","excerpt":"I am creating a sheet to be used as a template for tracking tasks per contracts. For invoices, I would like to have a section that will have Invoice 1, 2, -12 but I would like to just be able to input one date and then have the other dates flow in once the first date is entered. So if Invoice 1 gets a date due of 01\/15\/20…","categoryID":322,"dateInserted":"2020-04-14T20:19:07+00:00","dateUpdated":null,"dateLastComment":"2020-04-15T19:10:08+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-08T16:35:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-08T16:35:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":204,"score":null,"hot":9,"url":"https:\/\/community.smartsheet.com\/discussion\/67430\/date-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/67430\/date-formula","format":"rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-04-15T14:26:24+00:00","dateAnswered":"2020-04-14T22:34:45+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":67421,"type":"question","name":"SUMIFS coming back unparseable","excerpt":"I'm trying to add up a total amount based on a column called 'Total Amount (USD)' and relate that total amount to a column called 'Region' but only if the column called 'Stage' is equal to \"Proposed\", or \"Committed\", or \"Executing\". Am I at all close to accomplishing what I'm trying to do with SUMIFS below? =SUMIFS([Total…","categoryID":322,"dateInserted":"2020-04-14T18:04:25+00:00","dateUpdated":null,"dateLastComment":"2020-04-15T18:07:31+00:00","insertUserID":120519,"insertUser":{"userID":120519,"name":"Dennis Ruff","url":"https:\/\/community.smartsheet.com\/profile\/Dennis%20Ruff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-02-03T16:01:05+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-07-24T00:26:13+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\/67421\/sumifs-coming-back-unparseable","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/67421\/sumifs-coming-back-unparseable","format":"rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-04-15T17:52:44+00:00","dateAnswered":"2020-04-14T18:39:49+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":67450,"type":"question","name":"COUNTIFS","excerpt":"I am trying to count number of appointments by quarter by territory for a particular rep. The rep may be in several territories. This is the formula I had been using but now need to count another territory, SLG AG. Formula #1 without counting SLG AG is successful =COUNTIFS({Activity Metrics Range 3}, \">=SLG PS1\", {Activity…","categoryID":322,"dateInserted":"2020-04-15T16:29:42+00:00","dateUpdated":null,"dateLastComment":"2020-04-15T17:26:41+00:00","insertUserID":96501,"insertUser":{"userID":96501,"name":"Karen Passmore","url":"https:\/\/community.smartsheet.com\/profile\/Karen%20Passmore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!cxPDh70y_zw!5G_D-fkKZX4!6pq2ahaWT25","dateLastActive":"2021-06-17T01:48:46+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-07-24T00:26:13+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\/67450\/countifs","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/67450\/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":67397,"type":"question","name":"INDEX\/MATCH to retrieve several cells","excerpt":"Hi I am using the following formula =INDEX({Source Sheet Range 1}, MATCH([Dev Resource]1, {Source Sheet Range 3}, 1)) to retrieve data from SourceSheet to MJ2 sheet. The formula retrieves only one record when I need to retrieve all records associated with MJ. Anybody ran into this issue? Thanks Xav..","categoryID":322,"dateInserted":"2020-04-13T23:45:22+00:00","dateUpdated":null,"dateLastComment":"2020-04-15T11:51:14+00:00","insertUserID":55341,"insertUser":{"userID":55341,"name":"X M","url":"https:\/\/community.smartsheet.com\/profile\/X%20M","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!Uu9d30RDeXo!26_d_3xa534!kxGh2LEzoKp","dateLastActive":"2022-09-22T14:22: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-07-24T00:26:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":103,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/67397\/index-match-to-retrieve-several-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/67397\/index-match-to-retrieve-several-cells","format":"rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/113\/JD93N7EDJ270.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screen Shot 2020-04-13 at 16.12.41.png"},"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":67422,"type":"question","name":"Display text in a countif formula","excerpt":"I have a project type column and I want to show in the summary row at the top of my sheet the number of PoCs and Pilots. Instead of just displaying the number, I want the cell to read PoCs = 22 or Pilots = 15 =(COUNTIF([Project Type]3:[Project Type]69, =\"PoC\"))","categoryID":322,"dateInserted":"2020-04-14T18:05:46+00:00","dateUpdated":null,"dateLastComment":"2020-04-15T11:48:42+00:00","insertUserID":120520,"insertUser":{"userID":120520,"name":"Carla H","url":"https:\/\/community.smartsheet.com\/profile\/Carla%20H","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-04-16T17:06:08+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-07-24T00:26:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":561,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/67422\/display-text-in-a-countif-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/67422\/display-text-in-a-countif-formula","format":"rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-04-14T21:15:37+00:00","dateAnswered":"2020-04-14T18:37:21+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"},{"tagID":319,"urlcode":"functionality","name":"functionality"}],"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":67382,"type":"question","name":"Trying to make a Rolling 12 sum formula","excerpt":"Use Case: Have a list of number by a date. Looking to calculate a \"Rolling 12\", which would be the current month's number, plus the previous 11 months number. For example, if we are currently in April, the formula needs to sum months May2019 through April2020 I am replacing an Excel sheet, with the following correct…","categoryID":322,"dateInserted":"2020-04-13T19:45:46+00:00","dateUpdated":null,"dateLastComment":"2020-04-15T11:43:07+00:00","insertUserID":120474,"insertUser":{"userID":120474,"name":"MCSmcat20","url":"https:\/\/community.smartsheet.com\/profile\/MCSmcat20","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-04-20T13:21:59+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-07-24T00:26:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":12,"countViews":1945,"score":null,"hot":12,"url":"https:\/\/community.smartsheet.com\/discussion\/67382\/trying-to-make-a-rolling-12-sum-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/67382\/trying-to-make-a-rolling-12-sum-formula","format":"rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/738\/FN3XSHFOAKKF.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-04-14T15:47:31+00:00","dateAnswered":"2020-04-14T14:35:22+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