Countifs, Does not contain for Multi-select fields
Hi,
I'm trying to add several does not contains for a countif and I can't seem to find where I'm going wrong. I keep getting #invalid operation.
My goal is to filter the field for the following:
- Youth or Adult = Adult
- New or Continuum = New Enrollment
- Date Processed = Is Blank
- Parent coordinator (multi select drop down) = DOES NOT CONTAIN - None, Waiting for Assigment, Exited the program & Student(s) Denied.
I'm successfully able to filter for the first three filters and one of the fourth filter, but when I add the remaining DNC filters, I get an error.
=COUNTIFS({Enfrmcheckin - youth or adult}, HAS(@cell, "Adult"), {Enfrmcheckin - new or contin student}, HAS(@cell, "New Enrollment"), {Enfrmcheckin - parent coordinator}, NOT(CONTAINS("NONE", @cell)) + NOT(CONTAINS("Waiting for Assignment", @cell)) + NOT(CONTAINS("Exited the program", @cell)) + NOT(CONTAINS("Student(s) Denied")) + {Enfrmcheckin - Insch date proc in ETO}, <>"")
Thanks!
Answers
-
Is it possible more than one option will need to be selected in a single cell? What are the remaining options that would be counted?
-
Yes. There are 4 other remaining options in the multi-choice selection. There is a small chance that more than one option could be chosen of those remaining 4. The other fields are single-choice drop-down fields.
-
=COUNTIFS( {Enfrmcheckin - youth or adult}, HAS(@cell, "Adult"), {Enfrmcheckin - new or contin student}, HAS(@cell, "New Enrollment"), {Enfrmcheckin - parent coordinator}, or(@cell (NOT(CONTAINS("NONE", @cell))), or(@cell (NOT(CONTAINS("Waiting for Assignment", @cell))), or(@cell (NOT(CONTAINS("Exited the program", @cell))), or(@cell (NOT(CONTAINS("Student(s) Denied"))), {Enfrmcheckin - Insch date proc in ETO}, <> "")
...
-
Give this a try:
=COUNTIFS({Enfrmcheckin - youth or adult}, HAS(@cell, "Adult"), {Enfrmcheckin - new or contin student}, HAS(@cell, "New Enrollment"), {Enfrmcheckin - parent coordinator}, AND(NOT(CONTAINS("NONE", @cell)), NOT(CONTAINS("Waiting for Assignment", @cell)), NOT(CONTAINS("Exited the program", @cell)), NOT(CONTAINS("Student(s) Denied", @cell))), {Enfrmcheckin - Insch date proc in ETO}, <>"")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!