COUNTIF from a multiple drop down, exclude a word using NOT
Hi I'm trying to create a COUNTIF from a multiple drop down but run in to issues as following words are used “Not Accepted” and “Accepted”. I can count the “Not Accepted” by just using (CONTAINS(“Not” but I can’t count only “Accepted” as both “Not Accepted” and “Accepted” will be counted. I did try to use NOT(CONTAINS but without success.
I get an error on following test.
=COUNTIFS([RevShare Eligibility]1:[RevShare Eligibility]31; (CONTAINS("Accepted"; NOT(CONTAINS("Not"; @cell)))))
Best Answer
-
Nope. That was my fault.
In my region we use commas in between sections of formulas. Your region appears to use semicolons. I accidentally threw a comma in there between the two arguments for the AND statement.
Try this:
=COUNTIFS([RevShare Eligibility]1:[RevShare Eligibility]31; AND(NOT(CONTAINS("Not"; @cell)); CONTAINS("Accepted"; @cell)))
Answers
-
=COUNTIFS([RevShare Eligibility]1:[RevShare Eligibility]31; NOT(CONTAINS("Not", @cell)))
Give that a try.
-
The formula you recommend count every cell, including blank cells. What I'm looking for is to count "Accepted" but not include "Not Accepted". I need somehow create a countif formula that counting cells that only include the word "Accepted" but not "Not Accepted" It's tricky. thanks a lot.
-
=COUNTIFS([RevShare Eligibility]1:[RevShare Eligibility]31; AND(NOT(CONTAINS("Not"; @cell)), CONTAINS("Accepted"; @cell)))
-
Thanks. I get #UNPARSEABLE on that string but if I remove following, AND(NOT(CONTAINS("Not"; @cell)), it runs, so it's seems to be something in the AND(NOT(CONTAINS("Not"; @cell)), part that don't work properly
-
Nope. That was my fault.
In my region we use commas in between sections of formulas. Your region appears to use semicolons. I accidentally threw a comma in there between the two arguments for the AND statement.
Try this:
=COUNTIFS([RevShare Eligibility]1:[RevShare Eligibility]31; AND(NOT(CONTAINS("Not"; @cell)); CONTAINS("Accepted"; @cell)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!