Using COUNTIFS and NOT together
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?
Best Answer
-
What is the error you are getting?
Answers
-
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")
-
Thanks Paul. Unfortunately, that isn't working either. I tried it with using @cell <> "MIP" and with using <> "MIP"
-
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
-
Thank you Mike for looking at this. Unfortunately, neither of those methods are working for me.
-
What is the error you are getting?
-
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"))
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
-
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!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!