Add up all PAST DUE Items

=COUNTIFS([End Date]:[End Date], <= TODAY())<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"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}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107098,"type":"question","name":"HAS and Children Functions","excerpt":"I have the following column1 formula: =IF(HAS(CHILDREN([Column2]@row), \"Text\"), 1, 0) which sets the value of column1 to 1 if \"Text\" is found in any children in Column2 This formula works well for a single nested file of children. However, I have a multi-level nested file (i.e. Parent1, Child1, Parent2, Child2) and it…","snippet":"I have the following column1 formula: =IF(HAS(CHILDREN([Column2]@row), \"Text\"), 1, 0) which sets the value of column1 to 1 if \"Text\" is found in any children in Column2 This…","categoryID":322,"dateInserted":"2023-06-29T18:01:34+00:00","dateUpdated":"2023-06-29T18:25:44+00:00","dateLastComment":"2023-06-29T19:48:55+00:00","insertUserID":158378,"insertUser":{"userID":158378,"name":"Mike Dufour","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Dufour","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DKvTQ7i6zPw!HGjTH6jjgiQ!wvw2rujGLYm","dateLastActive":"2023-06-29T19:48:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":158378,"lastUserID":158378,"lastUser":{"userID":158378,"name":"Mike Dufour","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Dufour","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DKvTQ7i6zPw!HGjTH6jjgiQ!wvw2rujGLYm","dateLastActive":"2023-06-29T19:48:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":41,"score":null,"hot":3376131029,"url":"https:\/\/community.smartsheet.com\/discussion\/107098\/has-and-children-functions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107098\/has-and-children-functions","format":"Rich","lastPost":{"discussionID":107098,"commentID":383208,"name":"Re: HAS and Children Functions","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383208#Comment_383208","dateInserted":"2023-06-29T19:48:55+00:00","insertUserID":158378,"insertUser":{"userID":158378,"name":"Mike Dufour","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Dufour","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DKvTQ7i6zPw!HGjTH6jjgiQ!wvw2rujGLYm","dateLastActive":"2023-06-29T19:48:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T19:50:03+00:00","dateAnswered":"2023-06-29T18:48:24+00:00","acceptedAnswers":[{"commentID":383194,"body":"

CHILDREN will only look at entries one level down. DESCENDANTS will look at all levels below current.<\/p>"},{"commentID":383208,"body":"

Thank you Carson, very helpful!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"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}],"tags":[]},{"discussionID":107085,"type":"question","name":"Using INDEX\/MATCH to pull a date and CountIF not recognizing as a date","excerpt":"Hi, I'm using INDEX\/MATCH to pull a date from one report into another based on the same ID number. If there is no match, an error message populates. =IFERROR(INDEX({Corporate CAPA Closed Effective Date}, MATCH([InfoCard Number]@row, {Corporate CAPA Closed InfoCard Number}, 0)), \"CAPA Not Closed\") Once the formula is…","snippet":"Hi, I'm using INDEX\/MATCH to pull a date from one report into another based on the same ID number. If there is no match, an error message populates. =IFERROR(INDEX({Corporate CAPA…","categoryID":322,"dateInserted":"2023-06-29T15:11:26+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T20:28:41+00:00","insertUserID":146974,"insertUser":{"userID":146974,"name":"Jack Parry","url":"https:\/\/community.smartsheet.com\/profile\/Jack%20Parry","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T20:28:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":146974,"lastUser":{"userID":146974,"name":"Jack Parry","url":"https:\/\/community.smartsheet.com\/profile\/Jack%20Parry","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T20:28:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":24,"score":null,"hot":3376123207,"url":"https:\/\/community.smartsheet.com\/discussion\/107085\/using-index-match-to-pull-a-date-and-countif-not-recognizing-as-a-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107085\/using-index-match-to-pull-a-date-and-countif-not-recognizing-as-a-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107085,"commentID":383212,"name":"Re: Using INDEX\/MATCH to pull a date and CountIF not recognizing as a date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383212#Comment_383212","dateInserted":"2023-06-29T20:28:41+00:00","insertUserID":146974,"insertUser":{"userID":146974,"name":"Jack Parry","url":"https:\/\/community.smartsheet.com\/profile\/Jack%20Parry","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T20:28:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T20:28:12+00:00","dateAnswered":"2023-06-29T17:10:07+00:00","acceptedAnswers":[{"commentID":383157,"body":"

The INDEX function should be retaining the data type (in this case date value). It sounds like the issue is that there is at least one blank or no-date value within the range. To accommodate that you would use:<\/p>

<\/p>

=COUNTIFS({All Sites CAPA}, CONTAINS(\"CAPA\", @cell), {All Sites CAPA Effective Date}, IFERROR(<\/strong>MONTH(@cell), 0)<\/strong> = 6, {All Sites CAPA Closed On Time?}, CONTAINS(\"CAPA Overdue\", @cell))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"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}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">