IF with OR

PMOGal

I want to add to my formula for Past Due tasks:

=COUNTIFS([% Complete]:[% Complete], <1, Status:Status, <="Complete", Health:Health, <>"", Finish:Finish, <TODAY())

Status can be "Complete" or "Pending"

How do I revise my formula? Thank you.

Amber Eakin

Hi @PMOGal - Try this. Please let me know if it works!

=COUNTIFS([% Complete]:[% Complete], <1, Status:Status, OR(@cell="Complete", @cell="Pending"), Health:Health, <>"", Finish:Finish, <TODAY())

PMOGal

So sorry, I expressed my formula incorrectly. It should have been:

=COUNTIFS([% Complete]:[% Complete], <1, Status:Status, <>"Complete", Health:Health, <>"", Finish:Finish, <TODAY())

Status can be "Complete" or blank ("")

I tried your logic (which looks great) with:

=COUNTIFS([% Complete]:[% Complete], <1, Status:Status, OR(@cell <> "Complete", @cell <> ""), Health:Health, <>"", Finish:Finish, <TODAY())

Two of my cells satisfied the condition, but it returned a blank value. Thanks for your help.

FixFormula.JPG

PMOGal

I should add I am just trying to count the children rows.

Paul Newcome

If you are only trying to count child rows, then you would need to adjust your ranges

from

[Column Name]:[Column Name]

to

CHILDREN([Column Name]@row)

You would also need to change the OR arguments

from

<>

to

=

"<>" is saying "not equal to".

PMOGal

Thanks Amber/Paul. I got my formula to work after I made sure that % Complete had a 0% value in it.