Counting tasks that are late

jmpbell

I have a sheet where we record an anticipated complete date. We also record the status (not started, in progress, complete). 

We want to count the number of tasks that are late (we are past the complete date) and that have a status of either not started or in progress. How can I do that? I've been trying COUNTIFS but I can't get it to work. 

Thank you!

Shaine Greenwood

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.

Swamfax

Dear Shane,

I am trying to reference from another sheet, tasks that are in progress but late. I tried adapting the formula above but it is not working. Below are the example of the formulas I have tried so far, could you please assist me

=COUNTIF({Cloud Range 1}, "In Progress", "Not Started), [Due Date:Due Date], <Today())

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

Cheers!

Shaine Greenwood

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.

Swamfax

Thanks!

 

Swamfax

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.

Swamfax

Problem solved. Thanks!