# Count if Date is Past Due

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.

• 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

• ✭✭✭✭✭✭

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

• ✭✭

What about Past due tasks -the dates are in the past and the checkbox is unchecked. This is for columns and not rows? @Paul Newcome

• ✭✭✭✭✭✭

@Gamado I'm not sure what you're asking with that last bit about columns vs rows.

• ✭✭

@Paul Newcome I am trying to countif a column for past due tasks and a column with uncheckedbox. how do I go about it?

thanks,

• ✭✭✭✭✭✭

@Gamado You would use a straightforward COUNTIFS for this.

=COUNTIFS(range 1, criteria 1, range 2, criteria 2)

• ✭✭

=COUNTIFS([Target Date]:[Target Date], <TODAY(), Done:Done = 0)

this formula is not work.

• ✭✭✭✭✭✭

@Gamado You need a comma between the second range and the second criteria.