Counting tasks that are late

Hello,

Add the OR function to your COUNTIFS formula, for example:

=COUNTIFS(Status:Status, OR(@cell = "In Progress", @cell = "Not Started"), [Complete Date]:[Complete Date], < TODAY())

Make sure to change the column references in the above example to match the column names in your sheet.

Let me know if you have any questions on this.

Hello,

It looks like your syntax is a bit off. Details on how to use COUNTIFS are available here: https://help.smartsheet.com/function/countifs

Specifically the first part of your COUNTIFS appears to have 2 ranges, one for the count range and one for the criteria. COUNTIFS needs a single criteria to compare against the range.

You may also need to create multiple cross-sheet references (like one specifically for your Due Dates if they're on the other sheet as well).

I'm thinking something more like this:

=COUNTIFS({Status Range}, OR(@cell="In Progress",@cell="Not Started"), {Due Date Range}, @cell < TODAY())

NOTE: the above is just an example function and shouldn't be copied and pasted directly into a sheet. Please use it as a guide to get a feel for the structure of a COUNTIFS function.

Dear Shane,

I don't know if you can also assist me or give me a tip on how to adapt the below formula to generate a status update from a reference sheet.

I am trying to create a formula to generate a status update based on end date from a reference sheet (name: Cloud).  

=IF({Cloud Range 2},"Complete" <> 1, IF(TODAY() - [End Date] > 0, "Red", IF(TODAY() - [End Date] > -3, "Yellow", "Green"))))"

I have added the header of the sheet I am trying to reference from.

Many thanks in advance.