IFS Formula with Past Due, Not Completed


Hoping I can get some assistance on a formula for to give me a count if the following is met:

Count the # where: Type column is "Task" or "Sub-Task", Task Status column is "Not Started" or "In Progress" and End Date column is Today or In the Past

I would like to not add any additional columns.




  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Hi Olga,

    I'm not 100% certain what you're looking to do here, but if all you want is to check for a criteria match, then the following will do the job:

    =IF(AND(OR([Type]:[Type] = "Task", [Type]:[Type] = "Sub-Task"), OR([Task Status]:[Task Status] = "Not Started", [Task Status]:[Task Status] = "In Progress"), [End Date]:[End Date] <= TODAY()), "True", False")

  • Chris, thanks for pointing it out that my original question was not complete.

    I would like to get a count if the criteria that I have mentioned is met.




  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Hi Olga,

    You'll then need to use COUNTIFS. Without adding any more columns the formula will be much more convoluted than a simple nested IF as COUNTIFS does not support AND/OR statements, so we'd need to cheat with multiple COUNTIFS being added together. It will be a mess.

    I'd strongly suggest using the original formula above in a Check column and then simply adding the following formula into the cell you'd like to display the count (not in the Check, Task Status, Type or End Date columns):


    You can even hide the Check column so it will not be visible to users.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!