公式and Functions

0, COUNTIF(DESCENDANTS(), 1) \/ COUNT(DESCENDANTS()), 0) * 100,…","categoryID":322,"dateInserted":"2020-08-07T20:34:39+00:00","dateUpdated":null,"dateLastComment":"2020-08-07T22:44:37+00:00","insertUserID":111176,"insertUser":{"userID":111176,"name":"Anthony Barthelemy","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20Barthelemy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-01-24T21:26:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":111176,"lastUser":{"userID":111176,"name":"Anthony Barthelemy","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20Barthelemy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-01-24T21:26:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":178,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/70467\/checkbox-column-complete-column-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70467\/checkbox-column-complete-column-formula","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-07T22:49:21+00:00","dateAnswered":"2020-08-07T21:19: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":[],"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":70455,"type":"question","name":"Sheet Summary - SUM Chilldren Rows based on Status and Dates","excerpt":"Good Afternoon! I am trying to sum children rows where certain status' exist where the Pick-Up Date is today, within the next two days, and in the future beyond that. (Really, 3 formulas). I tried applying the following formula before involving dates to make sure the first part of logic is correct but it is giving me an…","categoryID":322,"dateInserted":"2020-08-07T17:15:56+00:00","dateUpdated":null,"dateLastComment":"2020-08-07T19:45:06+00:00","insertUserID":124960,"insertUser":{"userID":124960,"name":"Davis Dye","url":"https:\/\/community.smartsheet.com\/profile\/Davis%20Dye","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-08-11T20:24:43+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":124960,"lastUser":{"userID":124960,"name":"Davis Dye","url":"https:\/\/community.smartsheet.com\/profile\/Davis%20Dye","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-08-11T20:24:43+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":252,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/70455\/sheet-summary-sum-chilldren-rows-based-on-status-and-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70455\/sheet-summary-sum-chilldren-rows-based-on-status-and-dates","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/M20XQKFD04FJ\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-07T19:22:47+00:00","dateAnswered":"2020-08-07T18:22: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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":335,"urlcode":"sheet-summary","name":"Sheet Summary"}],"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":70461,"type":"question","name":"Display Value IF Between Two Dates","excerpt":"Hello, I'm trying to create a formula that will display a specific value if a date falls within a certain range. Any assistance would be greatly appreciated. If “Target Update or Replacement Date” is between 10\/01\/20 and 09\/30\/21, then \"Budget in Fiscal Year\" = “FY 20-21”. If “Target Update or Replacement Date” is between…","categoryID":322,"dateInserted":"2020-08-07T18:25:37+00:00","dateUpdated":"2020-08-07T18:27:31+00:00","dateLastComment":"2020-08-07T18:50:46+00:00","insertUserID":83081,"insertUser":{"userID":83081,"name":"saraha83081","url":"https:\/\/community.smartsheet.com\/profile\/saraha83081","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-02-25T18:44:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":83081,"lastUserID":16866,"lastUser":{"userID":16866,"name":"L_123","title":"","url":"https:\/\/community.smartsheet.com\/profile\/L_123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-18T20:18:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":630,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/70461\/display-value-if-between-two-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70461\/display-value-if-between-two-dates","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/A4YR84XSO6C4\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.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":70289,"type":"question","name":"#INVALID REF for Widget","excerpt":"Hi Community. I'm hoping someone can help me out. I created a task tracking sheet. A general rule is that our reporting month is always two month behind today. In my sheet summary I have a formula using index match, which works perfectly to my expected results. I am using a date reference table so I can see the month name…","categoryID":322,"dateInserted":"2020-07-31T19:39:34+00:00","dateUpdated":null,"dateLastComment":"2020-08-07T18:03:46+00:00","insertUserID":122145,"insertUser":{"userID":122145,"name":"Jamie Wisegerber","url":"https:\/\/community.smartsheet.com\/profile\/Jamie%20Wisegerber","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5SHNO5O8CI12\/nKNWU8GS14FGJ.jpg","dateLastActive":"2021-01-05T18:15:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":122145,"lastUser":{"userID":122145,"name":"Jamie Wisegerber","url":"https:\/\/community.smartsheet.com\/profile\/Jamie%20Wisegerber","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5SHNO5O8CI12\/nKNWU8GS14FGJ.jpg","dateLastActive":"2021-01-05T18:15:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":123,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/70289\/invalid-ref-for-widget","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70289\/invalid-ref-for-widget","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":70433,"type":"question","name":"SUMIFS Formula","excerpt":"Hi, I am trying to sum the numbers in the first column where the states are CA, CO, DC and IA. Could you please help. I a still learning formulas in Smartsheet. Thanks!","categoryID":322,"dateInserted":"2020-08-06T20:13:33+00:00","dateUpdated":null,"dateLastComment":"2020-08-07T17:53:21+00:00","insertUserID":124919,"insertUser":{"userID":124919,"name":"Irina Iatco","url":"https:\/\/community.smartsheet.com\/profile\/Irina%20Iatco","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!8wHXd9s9_vw!mllX3yc12To!FQsotNAEm0y","dateLastActive":"2021-05-14T19:10:24+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":124919,"lastUser":{"userID":124919,"name":"Irina Iatco","url":"https:\/\/community.smartsheet.com\/profile\/Irina%20Iatco","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!8wHXd9s9_vw!mllX3yc12To!FQsotNAEm0y","dateLastActive":"2021-05-14T19:10:24+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":124,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/70433\/sumifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70433\/sumifs-formula","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/4X4RA3DVMLCS\/sumifs-help.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"sumifs help.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":70450,"type":"question","name":"Date Range Formula","excerpt":"Hi, I'm trying to set up a formula to count the number of requests that were submitted between a specific date range. Below is the formula that I've written, however I'm still receiving an invalid operation error. Can anyone help me with this? =COUNTIFS({Citi Table Change Tracker Date Submitted} >= DATE(2019, 1, 1), {Citi…","categoryID":322,"dateInserted":"2020-08-07T15:35:45+00:00","dateUpdated":null,"dateLastComment":"2020-08-07T15:42:04+00:00","insertUserID":124951,"insertUser":{"userID":124951,"name":"Cassie Sprague","url":"https:\/\/community.smartsheet.com\/profile\/Cassie%20Sprague","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-08-25T16:52:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":124951,"lastUser":{"userID":124951,"name":"Cassie Sprague","url":"https:\/\/community.smartsheet.com\/profile\/Cassie%20Sprague","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-08-25T16:52:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":83,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/70450\/date-range-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70450\/date-range-formula","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-07T15:42:08+00:00","dateAnswered":"2020-08-07T15:40: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":[{"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":70410,"type":"question","name":"if then... do this query.","excerpt":"In the below - column Principal Check I have put: =if([Principal survey drawing required?]2=\"Yes\",[Principal drawing validated?]=\"Awaiting response\",\"Not Required\") I am wanting the Principal drawing validated? column to have an entry, \"Awaiting response\" based on Yes in column 'Principal survey drawing required?' column.…","categoryID":322,"dateInserted":"2020-08-06T08:37:44+00:00","dateUpdated":null,"dateLastComment":"2020-08-07T15:35:01+00:00","insertUserID":94521,"insertUser":{"userID":94521,"name":"vipa2000","url":"https:\/\/community.smartsheet.com\/profile\/vipa2000","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-04T08:00:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":93956,"lastUser":{"userID":93956,"name":"Brian Campbell","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Brian%20Campbell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/DD9DQEP8RM65\/nZX0DUWQ2IMP3.jpg","dateLastActive":"2022-08-04T19:51:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":74,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/70410\/if-then-do-this-query","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70410\/if-then-do-this-query","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/92T390WC4BVT\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.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":70436,"type":"question","name":"Cross Reference Count Formula with Multiple Criteria","excerpt":"Smartsheet Community: I am trying to Count If the Checkbox is Checked, the Date is Less than August 5 or Blank. I know I am close but can not see finish the Formula Correctly. Current Formula Below: =COUNTIFS({Issue \/ Defect UAT LOG Range 3}, \"1\", {Issue \/ Defect UAT LOG Range 4},

Help Article Resources