How to effectively use COUNTIF with multiselect column?

Faaez Kamaal

I'm trying to count each unique value selected in any given row of the column, however, the selected cell is coming back as one long string.

How do I count only the unique values, programmatically? I'm using the formulas shared for creating a list of distinct values given any column, if that helps (I can't remember where that post went to).

In my example, line 12 is "New Chair Install" and "Tickets". However, it's clearly coming through as just the one string as I mentioned earlier. Line 11 is "Remote Work" and "Tickets", but still a single string. I want them to be their own respective unique values. That's what I'm trying to solve.

2021-12-15_08-30-46.jpg

Paul Newcome

Are you pulling them from a multi-select dropdown? If so, there is a line break in between. You would need to substitute those out with commas when bringing it over.

=SUBSTITUTE(JOIN(DISTINCT(COLLECT({Visits}, {Visits}, @cell <> "")), ",") + ",", CHAR(10), ",")

Paul Newcome

How are you pulling the text strings in?

Faaez Kamaal

=JOIN(DISTINCT(COLLECT({Visits}, {Visits}, @cell <> "")), ",") + ","

Looks like a COLLECT function, that is further being parsed down by DISTINCT.

Paul Newcome

Are you pulling them from a multi-select dropdown? If so, there is a line break in between. You would need to substitute those out with commas when bringing it over.

=SUBSTITUTE(JOIN(DISTINCT(COLLECT({Visits}, {Visits}, @cell <> "")), ",") + ",", CHAR(10), ",")

Faaez Kamaal

That was it! Thank you!

Paul Newcome

Happy to help. 👍️