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

[Gina_Spitz]

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.

[ScottWorld · Accepted solution]

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)

[kuovonne]

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))

[Gina_Spitz]

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

[Gina_Spitz]

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

[Sean_Lake1]

Hum, Kuovonne, 

that didn't work for me, and since it's not a lookup field, rollups won't work
I have a text field, with pasted in emails, all separated by a comma, the field is "Who Showed up?" The formula field is "#Who Showed Up:

COUNTALL(ARRAYUNIQUE({Who Showed Up?}))

The result is 1 ;( I'll have to try Scott's option.  Shame not a simple "count" that works on strings without all the fancy LEN, etc. 😞