Count if Date is Past Due

Kimberlee Adam

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.

Kimberlee Adam

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.

Sarah_lee123

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

Thanks

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

Gamado

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

Paul Newcome

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

Gamado

@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,

Paul Newcome

@Gamado You would use a straightforward COUNTIFS for this.

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

Gamado

@Paul Newcome

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

this formula is not work.

Paul Newcome

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