Formulas and Functions

date requested\"Pending\"),If(date delivered…","categoryID":322,"dateInserted":"2021-02-02T18:13:50+00:00","dateUpdated":null,"dateLastComment":"2021-02-02T19:38:22+00:00","insertUserID":120850,"insertUser":{"userID":120850,"name":"Cindy Bowker","url":"https:\/\/community.smartsheet.com\/profile\/Cindy%20Bowker","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-04-06T18:50:12+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-07-12T20:38:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":71,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/75629\/if-statements","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/75629\/if-statements","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-02-02T19:17:56+00:00","dateAnswered":"2021-02-02T19:08:58+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":75570,"type":"question","name":"How do you calculate the amount of time between two dates but also include an IF?","excerpt":"For example, I want to calculate the amount of time between two dates but only specific to a particular column of data. In this case it is the duration for negotiating a Master Agreement with the Agreement type included in a separate column.","categoryID":322,"dateInserted":"2021-02-01T19:21:16+00:00","dateUpdated":null,"dateLastComment":"2021-02-02T19:30:16+00:00","insertUserID":131393,"insertUser":{"userID":131393,"name":"Kristin Rankin","url":"https:\/\/community.smartsheet.com\/profile\/Kristin%20Rankin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-02-05T18:48:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":131393,"lastUser":{"userID":131393,"name":"Kristin Rankin","url":"https:\/\/community.smartsheet.com\/profile\/Kristin%20Rankin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-02-05T18:48:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":112,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/75570\/how-do-you-calculate-the-amount-of-time-between-two-dates-but-also-include-an-if","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/75570\/how-do-you-calculate-the-amount-of-time-between-two-dates-but-also-include-an-if","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-02-01T23:09:41+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":75592,"type":"question","name":"Count Distinct from multiple criteria?","excerpt":"I have a data set similar to this sample which contains: Primary column with unique names Visited dates column formatted as DATE ClientID column - this has a many to one relationship with the Primary (Sandra and Mike can both work for the same Client) Results of the visit: Yes, Maybe Product discussed I'm trying to get a…","categoryID":322,"dateInserted":"2021-02-02T03:48:30+00:00","dateUpdated":"2021-02-02T03:53:17+00:00","dateLastComment":"2021-02-02T19:15:21+00:00","insertUserID":120060,"insertUser":{"userID":120060,"name":"Charlene Dewbre","url":"https:\/\/community.smartsheet.com\/profile\/Charlene%20Dewbre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-21T10:09:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":120060,"lastUserID":120060,"lastUser":{"userID":120060,"name":"Charlene Dewbre","url":"https:\/\/community.smartsheet.com\/profile\/Charlene%20Dewbre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-21T10:09:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":92,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/75592\/count-distinct-from-multiple-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/75592\/count-distinct-from-multiple-criteria","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/SBBXQDTJVLR3\/sample.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Sample.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-02-02T22:27:45+00:00","dateAnswered":"2021-02-02T19:15: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":[],"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":75616,"type":"question","name":"Formula needed - if a cell in column A matches that of any other cell in column A","excerpt":"Attempting a superbowl board this year virtually. I am in need of a formula that will tell me if any Cell in column A is an exact match of any other cell in column A - I would like tit to display \"DUPLICATE\" in another column.","categoryID":322,"dateInserted":"2021-02-02T15:57:47+00:00","dateUpdated":null,"dateLastComment":"2021-02-02T18:33:47+00:00","insertUserID":55236,"insertUser":{"userID":55236,"name":"Mtmoroni","url":"https:\/\/community.smartsheet.com\/profile\/Mtmoroni","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KQEEI6PTCM56\/nIL6Y3IWS6VW1.jpg","dateLastActive":"2023-07-12T21:05:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":55236,"lastUser":{"userID":55236,"name":"Mtmoroni","url":"https:\/\/community.smartsheet.com\/profile\/Mtmoroni","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KQEEI6PTCM56\/nIL6Y3IWS6VW1.jpg","dateLastActive":"2023-07-12T21:05:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":71,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/75616\/formula-needed-if-a-cell-in-column-a-matches-that-of-any-other-cell-in-column-a","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/75616\/formula-needed-if-a-cell-in-column-a-matches-that-of-any-other-cell-in-column-a","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-02-02T18:33:32+00:00","dateAnswered":"2021-02-02T17:07:58+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":75586,"type":"question","name":"Rolling total","excerpt":"Hi, I'm trying to create a total days column based on the following: start date - end date (ie., 10 days) 1\/1\/21 - 1\/10\/21 end date changes to new end date based on extension 1 (i.e., 20 days) 1\/1\/21 - extended to 1\/20\/21 extensions can happen at least 4 times (columns are TA EXT 1, TA EXT 2, TA EXT 3 (all are date fields)…","categoryID":322,"dateInserted":"2021-02-01T22:43:46+00:00","dateUpdated":null,"dateLastComment":"2021-02-02T17:23:27+00:00","insertUserID":130620,"insertUser":{"userID":130620,"name":"DHR Temp Assignment Team","url":"https:\/\/community.smartsheet.com\/profile\/DHR%20Temp%20Assignment%20Team","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jdFCIzFE1fo!ZGlAWXVVZbw!2LDxrTF9XjK","dateLastActive":"2022-09-28T21:30:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":130620,"lastUser":{"userID":130620,"name":"DHR Temp Assignment Team","url":"https:\/\/community.smartsheet.com\/profile\/DHR%20Temp%20Assignment%20Team","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jdFCIzFE1fo!ZGlAWXVVZbw!2LDxrTF9XjK","dateLastActive":"2022-09-28T21:30:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":72,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/75586\/rolling-total","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/75586\/rolling-total","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-02-02T06:54: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":75618,"type":"question","name":"How to cross reference two columns from one sheet to a data sheet. Counting by two data points.","excerpt":"Hi All, I am trying to count the number of product types (Column A), by Project Manager (Column D) and am always getting incorrect argument or inparseable. I can get the data if I just count the product types, but not when I want to only count product types by PM. I am putting this formula in a separate sheet (formula…","categoryID":322,"dateInserted":"2021-02-02T16:26:08+00:00","dateUpdated":null,"dateLastComment":"2021-02-02T17:10:31+00:00","insertUserID":131451,"insertUser":{"userID":131451,"name":"Frank Dean","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Dean","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DNPWA7nGzTw!u-1Xl0GpoKQ!quBwPP3AePu","dateLastActive":"2021-06-09T16:48:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":131451,"lastUser":{"userID":131451,"name":"Frank Dean","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Dean","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DNPWA7nGzTw!u-1Xl0GpoKQ!quBwPP3AePu","dateLastActive":"2021-06-09T16:48:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":264,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/75618\/how-to-cross-reference-two-columns-from-one-sheet-to-a-data-sheet-counting-by-two-data-points","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/75618\/how-to-cross-reference-two-columns-from-one-sheet-to-a-data-sheet-counting-by-two-data-points","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-02-02T16:54:43+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":75579,"type":"question","name":"Need a formula for counting something on a weekly basis.","excerpt":"From the screenshot below, I need a formula to count the incoming mail based on each week. I want to show the information on a dashboard on a weekly basis which will exclude the weekends. For example here, I have the data for last week of Jan, but when we enter into next week, first week of feb, I want the dashboard to…","categoryID":322,"dateInserted":"2021-02-01T20:42:00+00:00","dateUpdated":null,"dateLastComment":"2021-02-02T16:46:37+00:00","insertUserID":131414,"insertUser":{"userID":131414,"name":"Aparanji Veldurthy","url":"https:\/\/community.smartsheet.com\/profile\/Aparanji%20Veldurthy","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AAuE7mA1kPzwDGopU7A0cbVzLs0kKznnH8FDOwYlEedb","dateLastActive":"2021-02-12T19:05:42+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-07-12T21:05:23+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":376,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/75579\/need-a-formula-for-counting-something-on-a-weekly-basis","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/75579\/need-a-formula-for-counting-something-on-a-weekly-basis","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/HRJPDA3U3XZZ\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-02-02T21:39:20+00:00","dateAnswered":"2021-02-02T16:46:37+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":75286,"type":"question","name":"IF(Index-Match) - Multiple Entries in INDEX returns Non-Blank","excerpt":"I’m using an Index-Match formula and ran into an issue where the Source sheet (WIP Details) has multiple entries in the INDEX column (some entries w\/ JobCtMo populated, some with JobCtMo blank). I want to capture only the entries where JobCtMo is NOT BLANK. I used the formula below, but it is returning the #INVALID DATA…","categoryID":322,"dateInserted":"2021-01-26T11:53:21+00:00","dateUpdated":null,"dateLastComment":"2021-02-02T16:34:30+00:00","insertUserID":131092,"insertUser":{"userID":131092,"name":"Julie Nelson","url":"https:\/\/community.smartsheet.com\/profile\/Julie%20Nelson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-10T18:48:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":131092,"lastUser":{"userID":131092,"name":"Julie Nelson","url":"https:\/\/community.smartsheet.com\/profile\/Julie%20Nelson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-10T18:48:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":223,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/75286\/if-index-match-multiple-entries-in-index-returns-non-blank","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/75286\/if-index-match-multiple-entries-in-index-returns-non-blank","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-02-02T16:33:40+00:00","dateAnswered":"2021-01-28T05:09:23+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