Count Collect formula assistance needed

dhall

Hey all,

I'm using a Count Collect formula I grabbed from the PMO template as a baseline. Here's the original formula:

=COUNT(COLLECT({Task ID}, {Task Status}, "In Progress", {Task Health}, $Label@row))

Only thing I changed from the PMO is instead of "Project" I changed it to "Task". I'm trying to get similar metrics on individual tasks that don't fall within projects.

What I'm struggling with is modifying the "Collect" criterion (currently "In Progress") to look for more than just "In Progress" status.

Here's what I tried that I was positive was going to work, but it returns a 1, when it should return a 0.

=COUNT(COLLECT({Task ID}, {Task Status}, OR({Task Status} <> "Complete", {Task Status} <> "Canceled"), {Task Health}, $Label@row))

Essentially all statuses that are not "Complete" nor "Canceled".

Let me know what I'm missing or if I can provide any additional information. Thanks!

dhall

I may have figured it out, but I would love a formula expert to confirm if my logic is sound.

So far, it seems to be working.

Here's what I have:

=COUNTIFS({Task Health}, $Label@row, {Task Status}, <>"Complete", {Task Status}, <> "Canceled")

So with CountIFS, if my understanding is correct, it's looking for the following:

First, if the Health column shows a certain column (yellow, red, blue, green, etc.)

Next, the Status cannot be "complete".

And lastly, the status cannot be "canceled".