Include a "does not contain" with a COUNTM


I want to COUNTM on a multi-select column but I do not want to include the Speaker Name of NO EXTERNAL FACULTY. So if that's the only selection in a row it should return a '0' and if it's in a row with a Joe Bloggs and Jane Doe, it returns a '2'.

Can you combine a COUNTM with a NOT CONTAINS or similar?

=COUNTM([SPEAKER NAME]@row, NOT(HAS("NO EXTERNAL FACULTY", @cell))) and =COUNTM([SPEAKER NAME]@row, NOT(HAS(@cell, "NO EXTERNAL FACULTY"))) give me a '2' when only NO EXTERNAL FACULTY is the only selection.

I get the same answer for NOT CONTAINS too so I'm stumped!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!