COUNT Missed Deadline by Month
SS Community,
Requesting help with a formula to count the quantity (#) of times a task (row) missed the completion deadline by the Month of the Actual Completion Date {ACD}.
So in this sample data pic example below: January would be 3, and February would be 2.
So far I have:
=COUNTIFS({ACD}, {ACD} > {DNB})
But this does not count by month.
Tried leveraging a helper column {MD}, but with the right formula, I do not think this is needed.
=COUNTIFS({Missed Deadline}, IFERROR({ACD}, 0) = 1)
Appreciate the help in advance.
Thank you.
Robert
Best Answer

Hey @Robert B
You'll need a helper column, a checkbox will do, to first do the evaluation within the row to indicate a missed deadline.
Missed Deadline Helper Checkbox added to your SOURCE sheet
=IF(ISDATE([Actual Completion Date (ACD)]@row), IF([Actual Completion Date (ACD)]@row>[Date Needed By (DNB)]@row, 1))
Then your COUNTIFS become
=COUNTIFS({ACD}, ISDATE(@cell), {ACD}, MONTH(@cell)=1, {Helper Missed Deadline}, 1)
If you are pulling the data into a metrics table you could have a column that contain the month numbers. Then you could refer to that [Month Number]@row instead of hardcoding in the Month number twelve times.
The table might look like:
Month Name  Count  Month Number
January 3 1
February x 2
and the formula becomes
=COUNTIFS({ACD}, ISDATE(@cell), {ACD}, MONTH(@cell)=[Month Number]@row, {Helper Missed Deadline}, 1)
If you are not working across two sheets, let me know as the syntax of the COUNTIFS formula will change
Kelly
Answers

Hey @Robert B
You'll need a helper column, a checkbox will do, to first do the evaluation within the row to indicate a missed deadline.
Missed Deadline Helper Checkbox added to your SOURCE sheet
=IF(ISDATE([Actual Completion Date (ACD)]@row), IF([Actual Completion Date (ACD)]@row>[Date Needed By (DNB)]@row, 1))
Then your COUNTIFS become
=COUNTIFS({ACD}, ISDATE(@cell), {ACD}, MONTH(@cell)=1, {Helper Missed Deadline}, 1)
If you are pulling the data into a metrics table you could have a column that contain the month numbers. Then you could refer to that [Month Number]@row instead of hardcoding in the Month number twelve times.
The table might look like:
Month Name  Count  Month Number
January 3 1
February x 2
and the formula becomes
=COUNTIFS({ACD}, ISDATE(@cell), {ACD}, MONTH(@cell)=[Month Number]@row, {Helper Missed Deadline}, 1)
If you are not working across two sheets, let me know as the syntax of the COUNTIFS formula will change
Kelly

Brilliant!, once again, thank you for your help @Kelly Moore !!
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!