Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.
Count Past Due Tasks
Hi there - I'm new to formatting / formulas in smartsheet (and excel for that matter) and haven't had much luck creating a formula that counts tasks that are past due. While able to use conditional formatting to visually identify these items I'm trying to roll this up into a project overview; the overview/dashboard - currently includes task count by owener, by status, etc.
Am I on the right track using a COUNTIF and TODAY type functions? Perhaps a formula that would assign a count or criteria to any dates that are greater than today and, from there use a COUNTIF to count up the number of those items? My attempts thus far have returend various errors.
I appreciate any assistnce the community can offer!
-C
Comments
-
You are on the right track with COUNTIF and TODAY functions. Try this...
Add a checkbox column to your sheet with a formula that will check the box if the corresponding due date is in the past.
=IF([End Date]1 < TODAY(), 1)
Then build a COUNTIF that counts the number of boxes checked.
=COUNTIF(CB:CB, 1)
CB is the name of the checkbox column. By not including row numbers, it will count the entire column and not just a range.
-
I am trying to do something very similar. I would like to do it without adding additional columns. Can anyone tell me why this formula will not work or what can be done to correct it.
=COUNTIF([Due Date]:[Due Date], - TODAY(),>0)
I get #MISSING OR INVALID PARAMETERS
I have also tried this with same results.
=COUNTIF([Due Date]:[Due Date],
-
It will not work because you can only use < or > in a COUNTIF with numerical values - you cannot use dates.
From thehelp center article on formulas:
- Example: =COUNTIF(Done:Done, 1)
- Result: 2
- Example: =COUNTIF(Cost:Cost, ">" + 200)
NOTE: This syntax works for numeric values only. - Result: 2
-
瓦尔 ✭
Hi Chris, can you show us a sample of the formulas, My skills are less than yours and I am trying to do some similar to what you did. I will really appreciate that.
Thanks
Categories
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+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":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"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":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109500,"type":"question","name":"Auto-populate emails on a \"contact list\" column","excerpt":"I have a Form created for internal employees where they can submit request tickets to my department. We all have an account with Smartsheet. One of our columns (\"Requestor Name\") is in the Form where they type in their names. The \"Requestor Name\" column is a Contac List column. My issue here is, how can I have Smartsheet…","snippet":"I have a Form created for internal employees where they can submit request tickets to my department. We all have an account with Smartsheet. One of our columns (\"Requestor Name\")…","categoryID":321,"dateInserted":"2023-08-25T21:43:36+00:00","dateUpdated":"2023-08-25T21:44:14+00:00","dateLastComment":"2023-08-25T22:14:15+00:00","insertUserID":164850,"insertUser":{"userID":164850,"name":"ocanel","title":"Analyst","url":"https:\/\/community.smartsheet.com\/profile\/ocanel","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-27T06:55:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":164850,"lastUserID":165374,"lastUser":{"userID":165374,"name":"Shanky Paul","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Shanky%20Paul","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtcAdE60g-MBEj1rR28GVz9zn6RmbZsmEQm5EiEV=s96-c","dateLastActive":"2023-08-27T08:29:18+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":25,"score":null,"hot":3386002071,"url":"https:\/\/community.smartsheet.com\/discussion\/109500\/auto-populate-emails-on-a-contact-list-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109500\/auto-populate-emails-on-a-contact-list-column","format":"Rich","tagIDs":[204,247,334,448,527],"lastPost":{"discussionID":109500,"commentID":392677,"name":"Re: Auto-populate emails on a \"contact list\" column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392677#Comment_392677","dateInserted":"2023-08-25T22:14:15+00:00","insertUserID":165374,"insertUser":{"userID":165374,"name":"Shanky Paul","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Shanky%20Paul","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtcAdE60g-MBEj1rR28GVz9zn6RmbZsmEQm5EiEV=s96-c","dateLastActive":"2023-08-27T08:29:18+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-27T06:55:03+00:00","dateAnswered":"2023-08-25T22:14:15+00:00","acceptedAnswers":[{"commentID":392677,"body":"
Hi, <\/p>
Add a \"Created By\" column in your sheet and turn on \"Required smartsheet login to access your form\" in your form setting. So that whoever fills out the form, their email id will be automatically captured in \"created by\" column. This will replace your \"Requestor Name\" column. <\/p>
Thank you!<\/p>
<\/p>
![\"Screen](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/8138SXSFVML6\/screen-shot-2023-08-26-at-3-36-45-am.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
