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:

  1. Youth or Adult = Adult
  2. New or Continuum = New Enrollment
  3. Date Processed = Is Blank
  4. 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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.

  • heyjay
    heyjay ✭✭✭✭✭
    =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}, <> "")
    

    ...

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!