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.
Answers
-
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")
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!