# CountIf's with a date

Hello-

I am trying to get a formula to calculate if the task is still open and the date is before today's date then the task is overdue. This is what I have for the cell and it keeps returning unparseable:

=COUNTIFs({NITD Action Log Range 2}, "Open", [{NITD Action Log Range 3}, <=TODAY() ]

Range 2 is a column stating if the task is Open or Closed and Range 3 is a due date column.

Can someone help with this formula?

Thank you!!

Tags:

• Hi asuyama,

You're almost there. You just needed to get rid of the square brackets:

=COUNTIFS({NITD Action Log Range 2}, "Open", {NITD Action Log Range 3}, <=TODAY())

I'm assuming that the 2 NITD Action Log ranges are on another sheet?

• Yes it was reference to another sheet. Thank you so much for your help! I do have another one:

I am trying to figure out the average time a task is open, I have a task assigned date column and a due date column and a task open/closed column. What would the formula for that look like?

Thanks!

• Hi asuyama,

You are more than welcome.

To work out the average, I'd suggest making your life easier and provide more granularity by adding 3 hidden columns, then placing the following formulas in the first data row of the appropriate column and finally dragging it down as far as needed:

• Assigned to Open

• Open to Close

• Due to Closed