How to use a countif formula excluding specific criteria?

Ideally I am looking to write a countifs formula to represent the reporting teams count of active task by complexity, priority, and status with the exception that the completed and back log items should not be included in the active tasks total as they are not truly active

I was able to write a formula however my formula includes completed and backlog therefor the data I am reporting out is not true/correct

Looking to show that the reporting group has 1 active low complexity task due to the fact out of all of the low complexity tasks only one is truly active


  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    This is the basic idea.

    =COUNTIFS([Group]:[Group], "Reporting", [Complexity]:[Complexity] = "Low Complexity", [Priority]:[Priority] <> "Backlog", [Status]:[Status] <> "Backlog", [Done]:[Done] = 0)

    Whether you are putting this somewhere in the same sheet, a different sheet, or in the Sheet Summary will determine the exact syntax.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭


    means "not equal to".

    This = That --> This equals That

    This <> That --> This is not equal to That

  • Hollie Green
    Hollie Green ✭✭✭✭✭

    =Countifs([Priority]:[Priority],"Priority 1",[Status]:[Status],<>"Completed",[Complexity]:[Complexity],"Low Complexity")

    The above formula will count all Priority 1 with a low complexity that is not completed. It will exclude the completed with the <>"Completed" part of the formula and will exclude the backlog because it is only looking for "Priority 1" and all of you backlog are marked in that column.

    You can adjust the formula to get your other counts of Priority and Complexity by changing the criteria "Priority 1" and "Low Complexity"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!