Counting # of Overdue
Hi, I am trying to count the number of Overdue rows using two deadline columns (Due Date 1, Due Date 2) and a Status column, so using both of these criteria:
- Determine if the deadline has passed from the earlier of the two deadlines
- Eliminate from the count any past due rows also marked as Completed in the Status column (this column is a dropdown)
Some of the cells in either Due Date column may be blank (those should just be ignored for this purpose).
Could build this in either the metrics sheet or the sheet summary.
Thanks!
Answers
-
Are you able to provide some screenshots for context?
-
Hi Paul, thanks for your help! This is an example set up. The Status/Due Date 1/Due Date 2 data is in the main grid sheet so also getting a bit tripped up on how I would do those counts via the "Reference Another Sheet" in the metrics sheet- or via an "all-in-one" formula in the sheet summary. Ultimately, I'm trying to arrive at a single total number (after all the criteria are met) to use as a data source for the Dashboard.
Thanks!
-
I would suggest a helper column that pulls in the earlier of the two dates.
=MIN([Due Date 1]@row, [Due Date 2]@row)
Then you would reference the helper column in your formula.
=COUNTIFS([Helper Column]:[Helper Column], @cell < TODAY(), Status:Status, @cell <> "Complete")
-
Hi Paul, when I tested the helper formula in the grid sheet, it gave me Invalid Column Value in each instance. Do you how I could correct this (and ideally via the metrics sheet instead of in the grid sheet itself)?
Thanks!
-
Hi Paul, I figured out my error- the helper column needs to be formatted as Date only as well, so I made that change and the helper column works great now. I was also able to set up the second formula in metrics sheet now and that works great as well, thank you! My only remaining question would be if there's a way to move to the helper column/data to the metrics sheet (instead of the main grid sheet)?
Thanks again!
-
You may be able to use...
=COUNTIFS({First Date Column}, @cell< TODAY(), {Status}, @cell <> "Complete") + COUNTIFS({Second Date Column}, @cell< TODAY(), {Status}, @cell <> "Complete") - COUNTIFS({First Date Column}, @cell< TODAY(), , {Second Date Column}, @cell< TODAY(), {Status}, @cell <> "Complete")
-
Paul, yes! It didn't work at first try but I noticed that there's an extra comma at the end of the second line so I took that out and once I did it gave me the same count as the two-step process in your first response. Wow- thank you so so much!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!