COUNT Missed Deadline by Month

Robert B · 01/21/22

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

Kelly Moore · 01/21/22

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

Kelly Moore · 01/21/22