IF with OR

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.

Tags:

Answers

  • Amber Eakin
    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())

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • PMOGal
    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.


  • PMOGal
    PMOGal ✭✭✭✭

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

  • Paul Newcome
    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
    PMOGal ✭✭✭✭

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