Use OR in a formula

Hello. I am working on a formula and I have most of it complete. I have never used the OR function, but I think that is what I need to make this work correctly. Maybe it's just me, but I never really understand how to use it from just the Smartsheet examples. Here is my current formula:

=COUNTIFS({WO Tickets Database_CommNameShort}, [Community Name]@row, {WO Tickets Database_ClosedCancelledDate}, ="")

Basically, I am counting the number of rows where "WO Tickets Database_CommNameShort" equals the "[Community Name]@row" and the "WO Tickets Database_ClosedCancelledDate" is blank. I want to add another criterion to this formula where "WO Tickets Database_Status" equals "Closed" or "Cancelled". It might be able to be written so that it includes anything status other than "Open."

Thanks for the help. It is always appreciated.

Tags:

Answers

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭

    I think that it could be done either way

    =COUNTIFS({WO Tickets Database_CommNameShort}, [Community Name]@row, OR({WO Tickets Database_ClosedCancelledDate} ="",{WO Tickets Database_ClosedCancelledDate} ="Closed",{WO Tickets Database_ClosedCancelledDate} = "Cancelled" ))


    =COUNTIFS({WO Tickets Database_CommNameShort}, [Community Name]@row, {WO Tickets Database_ClosedCancelledDate}, <>"Open")

  • M. David
    M. David ✭✭✭✭✭

    @Kimberly Loveless Thanks for the help. I ended up using the second of your two options with one tweak. I had to add back in the criterion about the ClosedCancelledDate being blank. I didn't know about the <> trick, and that's what I needed to make it all work. Does the <>"Open" basically mean everything except "Open"? That's what it seems to be doing. Thanks again for the help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!