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

k.platt

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

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

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!

k.platt

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

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", ……………………………