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

Solved
1634 0
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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.

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

1 Solution

Accepted Solutions
18 - Pluto

Welcome to the community, @Gina_Spitz!

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

5 Replies 5
18 - Pluto

Welcome to the community, @Gina_Spitz!

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

18 - Pluto

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

5 - Automation Enthusiast

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

5 - Automation Enthusiast

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

8 - Airtable Astronomer

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. 😞