How to exclude values from a COUNTIF expression

07/31/19 Edited 12/09/19

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

Comments

  • 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!

  • Paul NewcomePaul 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.

  • edited 08/01/19

    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 or Register to comment.