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"))
Best Answer
-
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")))
Answers
-
Use countifs
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
-
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"))
-
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")))
-
THAT worked like a charm! Thank you, Brian!
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!