Count tasks due in the next 14 days with multiple status from another sheet

I am trying to reference another sheet and count the number of tasks that meet the following criteria:

Hieraracy@row should be Child

Status@row should be Not Started or In Progress

Finish@row (date) is in he next 14 days (from today)

I was successfully able to do the reverse (completed in the last 14 days) by using this:

=COUNTIFS({121 Task Status}, "Complete", {121 Task Hierarchy}, "Child", {121 Completed}, >=TODAY(-14))

I tried this but no go...

=COUNTIFS(OR({121 Task Status}, "Not Started", {121 Task Status}, "In Progress"), {121 Task Hierarchy}, "Child", {121 Finish Date}, <=TODAY(+14))

This worked but only showed me tasks specific to the singular status:

=COUNTIFS({121 Task Status}, "Not Started", {121 Task Hierarchy}, "Child", {121 Finish Date}, <=TODAY(+14))

I appreciate any help you can provide.


  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭

    Hi @k.platt

    This can be solved with a simple trick - counts two statuses separately and add them to get her:

    =COUNTIFS({121 Task Status}, "Not Started", {121 Task Hierarchy}, "Child", {121 Finish Date}, <=TODAY(+14)) + COUNTIFS( {121 Task Status}, "In Progress", {121 Task Hierarchy}, "Child", {121 Finish Date}, <=TODAY(+14))

    Let me know if this works?

    Good Luck!

    Tomasz Giba

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!