Counting # of Overdue

Allyson Sullivan

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:

1. Determine if the deadline has passed from the earlier of the two deadlines
2. 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!

Paul Newcome

Are you able to provide some screenshots for context?

Allyson Sullivan

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!

Paul Newcome

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")

Allyson Sullivan

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!

Allyson Sullivan

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!

Paul Newcome

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")

Allyson Sullivan

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!

Paul Newcome

Happy to help. 👍️