COUNTIFS Formula Based on Status and Due Date

Question

I'm looking to create a formula that will count the number of overdue tasks. This is based on the "assigned to", "status" and "due date".

My current formula is =COUNTIFS({[Sheet Name]}, <TODAY(), {[Sheet Name]}, ="[Assigned To]"). However, this is pulling tasks that are due in the past, but are completed.

[Deleted User] · Answer

@Jodi C try this format =COUNTIFS({Assigned To}, [Assigned To]@row , {Status}, <> "Complete", {Due Date}, < TODAY())

Jodi C · Answer

@AmarK

=COUNTIFS({[Sheet Name]}, "[Assigned To]", {[Sheet Name Status]} <> "Complete", {[Sheet Name Due Date]} < TODAY())

This is giving me #INVALID. What have I done wrong?

[Deleted User] · Answer

@Jodi C You need to add conditions for Status not completed and Due Date in the past.

Example:

=COUNTIFS({Status}, "Not Started", {Assigned To}, [Assigned To]@row , {Due Date}, <TODAY())

If you want to include multiple open statuses:

=COUNTIFS({Assigned To}, [Assigned To]@row , {Status}, <> "Complete", {Due Date}, <TODAY())

This will count only tasks assigned to the person, not completed, and with a due date before today.