Counting tasks that are late

jmpbell ✭✭✭✭
edited 12/09/19 in Smartsheet Basics

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!


  • 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.

  • 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())


  • Hello,

    It looks like your syntax is a bit off. Details on how to use COUNTIFS are available here:

    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.


  • Swamfax
    edited 11/12/18

    Problem solved. Thanks!