Formulas

=DATE(2023, 07, 01),<=DATE(2023. 07, 31)) Thank you!","categoryID":322,"dateInserted":"2023-07-25T17:34:48+00:00","dateUpdated":null,"dateLastComment":"2023-07-25T17:58:53+00:00","insertUserID":163926,"insertUser":{"userID":163926,"name":"N.Derkiss","url":"https:\/\/community.smartsheet.com\/profile\/N.Derkiss","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m6nQX5jhZD8!ZP1SWYSZYcU!4cnGa06kNhO","dateLastActive":"2023-08-25T21:12:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":75,"score":null,"hot":3380615021,"url":"https:\/\/community.smartsheet.com\/discussion\/108047\/help-with-a-countifs-formula-for-a-date-range","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108047\/help-with-a-countifs-formula-for-a-date-range","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-25T18:11:05+00:00","dateAnswered":"2023-07-25T17:58:53+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":108030,"type":"question","name":"Invalid Data Type on an IF function with Index Collect","excerpt":"Hi All, I am trying to nest and index collect into an IF but I keep on getting Invalid Data Type. =IF(INDEX(COLLECT({Tracker - Range 5}, {Tracker - Range 2}, [Project Name]@row, {Tracker - Range 5}, \"LATE\"), 1), \"LATE\", IF(INDEX(COLLECT({Tracker - Range 5}, {Tracker - Range 2}, [Project Name]@row, {Tracker - Range 5}, \"On…","categoryID":322,"dateInserted":"2023-07-25T14:22:16+00:00","dateUpdated":null,"dateLastComment":"2023-07-25T17:47:12+00:00","insertUserID":151347,"insertUser":{"userID":151347,"name":"MegBurns23","url":"https:\/\/community.smartsheet.com\/profile\/MegBurns23","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3JFev2akC_o!bRNeG5LETzw!9HYHhXoqNPO","dateLastActive":"2023-08-07T13:19:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":162305,"lastUser":{"userID":162305,"name":"Cory Page","title":"Revenue Cycle Analyst & Informatics","url":"https:\/\/community.smartsheet.com\/profile\/Cory%20Page","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T20:25:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":131,"score":null,"hot":3380608768,"url":"https:\/\/community.smartsheet.com\/discussion\/108030\/invalid-data-type-on-an-if-function-with-index-collect","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108030\/invalid-data-type-on-an-if-function-with-index-collect","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-25T17:42:25+00:00","dateAnswered":"2023-07-25T17:28:08+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":108037,"type":"question","name":"List distinct values based on a criteria and remove blanks","excerpt":"I am making a metrics sheet that calculates what I need to show from a datasheet. The datasheet has columns for the Type (building type is either MOB or Hospital) and Departments (MOB has 12 departments and Hospital has 24 departments) along with a bunch of other columns that have data. In my formulas {CD - } is…","categoryID":322,"dateInserted":"2023-07-25T15:46:43+00:00","dateUpdated":null,"dateLastComment":"2023-07-25T17:27:43+00:00","insertUserID":148304,"insertUser":{"userID":148304,"name":"Erin Hauser","url":"https:\/\/community.smartsheet.com\/profile\/Erin%20Hauser","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m1nqH8o5W7M!zCkoc25Zy7s!RbcZ7Uh87fD","dateLastActive":"2023-08-25T20:02:00+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":60,"score":null,"hot":3380607866,"url":"https:\/\/community.smartsheet.com\/discussion\/108037\/list-distinct-values-based-on-a-criteria-and-remove-blanks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108037\/list-distinct-values-based-on-a-criteria-and-remove-blanks","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/EGVWH88VHUKT\/screenshot-2023-07-25-104111.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-07-25 104111.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-26T11:34:07+00:00","dateAnswered":"2023-07-25T17:16: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"}],"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":108006,"type":"question","name":"Adding multiple SUMIFS and return is $0 if one cell is blank","excerpt":"I'm trying to add multiple sumifs formulas together in one formula. However, not all of the sumifs will have an input. Example: Expense tracking, most of the time there will be only one charge code to utilize. There are times when multiple charge codes can be utilized and I'm only trying to extract the amount for a…","categoryID":322,"dateInserted":"2023-07-24T23:33:42+00:00","dateUpdated":null,"dateLastComment":"2023-07-25T16:24:42+00:00","insertUserID":146506,"insertUser":{"userID":146506,"name":"Dave French","url":"https:\/\/community.smartsheet.com\/profile\/Dave%20French","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T16:31:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":146506,"lastUser":{"userID":146506,"name":"Dave French","url":"https:\/\/community.smartsheet.com\/profile\/Dave%20French","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T16:31:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":49,"score":null,"hot":3380545104,"url":"https:\/\/community.smartsheet.com\/discussion\/108006\/adding-multiple-sumifs-and-return-is-0-if-one-cell-is-blank","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108006\/adding-multiple-sumifs-and-return-is-0-if-one-cell-is-blank","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2023-07-24T23:59:08+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":78351,"type":"question","name":"Using a formula to assign multiple contacts","excerpt":"Good morning! I am having a difficult time using a formula to assign multiple contacts. I have the checkbox selected to allow multiple contacts, but it seems as though when it comes to a formula, the cell is only seeing it as text. Example: Cell1 - megan@test.com Cell2 - jones@test.com Formula: Cell1 + Cell2 This then just…","categoryID":322,"dateInserted":"2021-04-14T15:46:17+00:00","dateUpdated":null,"dateLastComment":"2023-07-25T16:22:59+00:00","insertUserID":130084,"insertUser":{"userID":130084,"name":"MeganJones","url":"https:\/\/community.smartsheet.com\/profile\/MeganJones","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3IFCfyEqi3o!BhlDAXUyNzw!a3nHGXYH5fx","dateLastActive":"2021-05-13T16:32:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":138637,"lastUser":{"userID":138637,"name":"StevenBlackburnMBA","title":"Director, Informatics Innovation","url":"https:\/\/community.smartsheet.com\/profile\/StevenBlackburnMBA","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/30MFCHQD4VJ8\/nA6GUCC5S5SQG.jpg","dateLastActive":"2023-07-30T03:08:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":538,"score":1,"hot":3308723056,"url":"https:\/\/community.smartsheet.com\/discussion\/78351\/using-a-formula-to-assign-multiple-contacts","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/78351\/using-a-formula-to-assign-multiple-contacts","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-04-14T16:03:57+00:00","dateAnswered":"2021-04-14T15:48:35+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":247,"urlcode":"contacts","name":"Contacts"},{"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":54331,"type":"discussion","name":"Need Help with Formula for Rolling Metrics","excerpt":"I am working on a 6 months rolling metrics calculation for a point system. A simple data involved will be as follows: 1) Date column 2) Points column (0-10) 3) Name column I need to calculate a 6 months rolling sum of points, for each person. I couldn't figure out how to calculate the sum of points for each person in 6…","categoryID":322,"dateInserted":"2019-08-05T05:22:04+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2023-07-25T14:51:03+00:00","insertUserID":39761,"insertUser":{"userID":39761,"name":"Vivien Chong","url":"https:\/\/community.smartsheet.com\/profile\/Vivien%20Chong","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!gx1fXwAkvsQ!Glje3-xv06Y!kk-3Zw9dLY7","dateLastActive":"2023-08-25T03:28:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":133636,"lastUser":{"userID":133636,"name":"Tina Davis","url":"https:\/\/community.smartsheet.com\/profile\/Tina%20Davis","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-23T15:59:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":1169,"score":null,"hot":3255285787,"url":"https:\/\/community.smartsheet.com\/discussion\/54331\/need-help-with-formula-for-rolling-metrics","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/54331\/need-help-with-formula-for-rolling-metrics","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":108029,"type":"question","name":"How to use COUNTIFS where there are 3 conditions (columns to be referenced)","excerpt":"I'm trying to create a formula in a metric sheet that is referencing another sheet where data is held. I've been able to set up a COUNTIFS formula that references 2 columns so far, but my issue is that it doesn't seem to let me reference a third column using the \"formula builder\" as below: I'm trying to use COUNTIFS to…","categoryID":322,"dateInserted":"2023-07-25T13:36:23+00:00","dateUpdated":null,"dateLastComment":"2023-07-25T14:30:28+00:00","insertUserID":78206,"insertUser":{"userID":78206,"name":"SteCoxy","url":"https:\/\/community.smartsheet.com\/profile\/SteCoxy","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!aTDbGqngcGQ!2wnbf5m83zw!VTDReGCjYpc","dateLastActive":"2023-08-21T11:32:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":78206,"lastUser":{"userID":78206,"name":"SteCoxy","url":"https:\/\/community.smartsheet.com\/profile\/SteCoxy","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!aTDbGqngcGQ!2wnbf5m83zw!VTDReGCjYpc","dateLastActive":"2023-08-21T11:32:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":103,"score":null,"hot":3380591211,"url":"https:\/\/community.smartsheet.com\/discussion\/108029\/how-to-use-countifs-where-there-are-3-conditions-columns-to-be-referenced","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108029\/how-to-use-countifs-where-there-are-3-conditions-columns-to-be-referenced","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/MY0PPUBHH402\/screenshot-2023-07-25-at-14-24-16.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-07-25 at 14.24.16.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-25T14:47:21+00:00","dateAnswered":"2023-07-25T14:12: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":[{"tagID":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":335,"urlcode":"sheet-summary","name":"Sheet Summary"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}],"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}">