Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

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

Topic Labels: Formulas
Solved
Jump to Solution
901 4
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

1 Solution

Accepted Solutions

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)

See Solution in Thread

4 Replies 4

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)

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

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

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