Count if Date is Past Due

07/01/19 Edited 12/09/19

I need a formula to count the number of tasks past due per person. I tried a Count Ifs formula and then I also tried using a checkbox. 

So in the example attached, I want to count all the overdue items in 1 Rough Draft Writing Due Date column per person.

Count overdue items.JPG

Comments

  • Never mind, I got it figured out. :) Just in case anyone else has the same question. Here is the formula.

    =IF(AND([1 Rough Draft Writing Due Date]11 < TODAY(), NOT([1 Rough Draft Writing Status]11 = "Completed")), 1, 0)

     

    Now, I do have to figure out how to not count it if the date cell is blank.

  • Hello there, Did you ever figure out the rest of your formula?

    Thanks

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Sarah_lee123 There are a few options to accomplish the above. Using the checkbox column and then counting that would look something along the lines of...

    =IF(AND([1 Rough Draft Writing Due Date]@row <> "", [1 Rough Draft Writing Due Date]@row < TODAY(), [1 Rough Draft Writing Status]@row <> "Completed"), 1)


    and then to get the count...

    =COUNTIFS([Checkbox Column]:[Checkbox Column], 1, [Name Column]:[Name Column], "John Doe")


    Or you could skip the checkbox column and go directly to the count with:

    =COUNTIFS([1 Rough Draft Writing Due Date]:[1 Rough Draft Writing Due Date], AND(@cell <> "", @cell < TODAY()), [1 Rough Draft Writing Status]:[1 Rough Draft Writing Status], @cell <> "Complete", [Name Column]:[Name Column], "John Doe")

Sign In or Register to comment.