How to exclude values from a COUNTIF expression

I'm writing a COUNTIF formula referencing a column with multiple values that works well, until I try to build an expression that says:

I want you to count the values in this column as long as they are NOT "x" OR "y" OR "z".

Can anyone help with the proper syntax? I've tried the following but it doesn't work:

COUNTIF ({Reference}, OR(@cell<>"x", @cell<>"y", @cell<>"z"))

Find more posts tagged with

Formulas

Project Management

Accepted answers

Brian W

Does it work with NOT?

=COUNTIFS({Request Type}, OR(@cell = "Enhancement Request", @cell = "NPI Request", @cell = "Reporting Request"), {Capability / Technology}, OR(@cell = "Other", @cell = "Reporting"), {Assigned To}, OR(@cell = "Tim", @cell = "Jason", @cell = "Jim"), {Approved}, "Approved", {Business Unit}, NOT(OR(@cell = "Appliances", @cell = "BCS", @cell = "RPS")))

All comments

Nick Burrus

Use countifs

https://help.smartsheet.com/function/countifs

Tim Möhle

Thanks for the reply. I am actually using a COUNTIFS expression, but the below does not work:

Section in BOLD is what is plaguing me.

=COUNTIFS({Request Type}, OR(@cell = "Enhancement Request", @cell = "NPI Request", @cell = "Reporting Request"), {Capability / Technology}, OR(@cell = "Other", @cell = "Reporting"), {Assigned To}, OR(@cell = "Tim", @cell = "Jason", @cell = "Jim"), {Approved}, "Approved", {Business Unit}, OR(@cell <> "Appliances", @cell <> "BCS", @cell <> "RPS"))

Brian W

Does it work with NOT?

Tim Möhle

THAT worked like a charm! Thank you, Brian!

Paul Newcome

Another option as opposed to NOT(OR(...........)) would be to switch the OR to an AND.

OR(@cell <> "x", @cell <> "f")

Technically it will count all of the "x"s because they aren't "f"s and will count all of the "f"s because they aren't "x"s.

Count if this cell is not equal to "x", or this cell is not equal to "f".

By flipping it to an AND...

AND(@cell <> "x", @cell <> "f")

Count if this cell is not equal to "x" and is also not equal to "f".

Hope the explanation makes sense.

Tim Möhle

Hey Paul, Thanks. I tested that one out too. Unfortunately it gave me a different result from the one expected. I kept the NOT (OR) solution in place.

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.