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.

Find more posts tagged with

Formulas

Accepted answers

All comments

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

@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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.