How to exclude values from a COUNTIF expression

Tim Möhle

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"))

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")))

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?

=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")))

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.