Count unique text (names) separated by commas in another formula field (arrayunique)

I extracted unique names of participants in my table by using the ARRAYUNIQUE formula in a field called {Progress Made BY}. This produced a list of unique names separated by commas in the {Progress Made BY} field, which is what I was looking for. I used this one instead of CONCATENATE because I had a couple of duplicate text strings.

I then created an IF LEN SUBSTITUTE formula field called {# Participants Progress Made BY} to count the names separated by commas produced in the {Progress Made BY} formula field.

IF({Progress Made BY},LEN({Progress Made BY})-LEN(SUBSTITUTE({Progress Made BY},",",""))+1)

*Note that I had used this formula successfully in other tables to do the same thing, however the reference field in the other formula fields was a CONCATENATE formula field instead of an ARRAYUNIQUE formula field.

I get an #ERROR message in the {# Participants Progress Made BY} field where there is data (separated by commas) in the {Progress Made BY} field in the record, and nothing returned where there’s no data in the {Progress Made BY} field in the record (the latter is not a problem).

I’m not sure what I’m doing wrong. Please advise! Thank you in advance.

Welcome to the community, @Gina_Spitz!

I’m not at my computer to test this out, but try this and see if it works:

IF({Progress Made BY},LEN({Progress Made BY}&"")-LEN(SUBSTITUTE({Progress Made BY}&"",",",""))+1)
2 Likes

Since you are using ARRAYUNIQUE you must have a rollup field. ARRAYUNIQUE by itself without ARRAYJOIN usually returns an array, not a text string. The LEN and SUBSTITUTE functions expect text strings. Scott’s technique of forcing the arrays into strings should work.

However, an easier way to get the count is to throw COUNTALL around your ARRAYUNIQUE directly in the rollup itself.

COUNTALL(ARRAYUNIQUE(values))

2 Likes

Thank you so much for this formula - it worked like a charm and is now counting the names properly! Appreciate this.

1 Like

Ah, that explains it! Scott’s solution DID work for me. Thank you as well.

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.