Using COUNTIFS and NOT together

Bobby Nelson

I am using the following formula and getting an error:

=COUNTIFS({Master Discrepancy Log 2021 Owner}, $[Primary Column]@row, {Master Discrepancy Log 2021 Product Type}, "Prime Share", {Master Discrepancy Log 2021 Month Opened}, 2, {Master Discrepancy Log 2021 Year Opened}, 2021, {Master Discrepancy Log 2021 Discrepancy Type}, NOT("MIP"))

The issue comes into play when I put in the NOT. Any idea on how to fix this?

Paul Newcome

What is the error you are getting?

Paul Newcome

Try this instead...

=COUNTIFS({Master Discrepancy Log 2021 Owner}, $[Primary Column]@row, {Master Discrepancy Log 2021 Product Type}, "Prime Share", {Master Discrepancy Log 2021 Month Opened}, 2, {Master Discrepancy Log 2021 Year Opened}, 2021, {Master Discrepancy Log 2021 Discrepancy Type}, @cell <> "MIP")

Bobby Nelson

Thanks Paul. Unfortunately, that isn't working either. I tried it with using @cell <> "MIP" and with using <> "MIP"

Mike Raposo

Hi Bobby,

The issue you're having is that the NOT function can only be used to provide the opposite of a Logical Expression and simply putting "MIP" in the parenthesis does not constitute a logical expression.

I believe there's two solutions here, the first being replacing ...NOT("MIP")) with ...NOT(@cell = "MIP"))

The second option would be to substitute ...NOT("MIP")) with ...@cell <> "MIP" as <> is the operator for not equal to.

Hope this helps! Let me know if you have any additional questions.

Best,

Mike

Bobby Nelson

Thank you Mike for looking at this. Unfortunately, neither of those methods are working for me.

Paul Newcome

What is the error you are getting?

Kelly Drake

Looking at the formula, I can't tell why it won't work - I've used NOT nested in COUNTIFS.

Are all the ranges coming from the same sheet??

I'm wondering if this would work:

=COUNTIFS({Master Discrepancy Log 2021 Owner}, $[Primary Column]@row, {Master Discrepancy Log 2021 Product Type}, "Prime Share", {Master Discrepancy Log 2021 Month Opened}, 2, {Master Discrepancy Log 2021 Year Opened}, 2021, {Master Discrepancy Log 2021 Discrepancy Type}, NOT({Master Discrepancy Log 2021 Discrepancy Type} = "MIP"))

Bobby Nelson

The weirdest thing. I tried the formula in a different cell and it worked. So, I copied and pasted from that cell and now it works. Here is the one that works:

=COUNTIFS({Master Discrepancy Log 2021 Owner}, $[Primary Column]@row, {Master Discrepancy Log 2021 Product Type}, "Prime Share", {Master Discrepancy Log 2021 Month Opened}, 2, {Master Discrepancy Log 2021 Year Opened}, 2021, {Master Discrepancy Log 2021 Discrepancy Type}, @cell <> "MIP")

Thank you Paul and everyone for helping!

Paul Newcome

Happy to help. 👍️