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.
Answers
-
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
Categories
Check out the Formula Handbook template!