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

k.platt
k.platt ✭✭✭
in Formulas and Functions

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.

· Share on FacebookShare on Twitter

Best Answer

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    Answer ✓

    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

    · Share on FacebookShare on Twitter

Answers

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    Answer ✓

    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

    · Share on FacebookShare on Twitter
  • k.platt
    k.platt ✭✭✭

    @Tomasz Giba - This did work! Thank you so much and I apologize for the late follow-up.

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can also use an OR inside of a COUNTIFS like so:

    =COUNTIFS({121 Task Status}, OR(@cell = "Not Started", @cell = "In Progress"), ………………………….)

    And if you only have 3 statuses such as Not Started, In Progress, or Complete, you can count everything that isn't Complete like so:

    =COUNTIFS({121 Task Status}, @cell <> "Complete", ……………………………

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!