Counting tasks that are late

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!

Find more posts tagged with

Education

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Landscape View in App for Android
I have an employee with an Android phone. He has just downloaded the Smartsheet app and cannot view sheets in landscape orientation. I have an iPhone, so I'm not much help. He tried changing his settings to "auto-rotate" but that did not work. Any ideas/solutions? Thanks!
Attachment/sheet size
This discussion was created from comments split from: Attachment/sheet size maximum limit.
How am I able to change timeline colors, when no color setting appears when I right click?
When I right click the only option I get is to edit and not to change the color settings, and I am looking to change the color!