Count unique values in a column

[Russell_Bishop1]

I’ve had to do this quite a few times… my old solution was:

1. Rollup with ARRAYUNIQUE(values)
2. Remove any trailing ','s in another column
3. Count the remaining commas, +1

Now I do it all inside the rollup with the following snippet:

gist.github.com

https://gist.github.com/RussellBishop/153d70be56ec5d8983439b6d9cf1279f

airtable-count-unique-array-rollup

IF((values), 

LEN(

IF(
    FIND(",", ARRAYJOIN(ARRAYUNIQUE(values), ",")&"") = 1, 
    SUBSTITUTE(ARRAYJOIN(ARRAYUNIQUE(values), ",")&"", ",", "", 1), 
        
    IF(
        FIND(",", ARRAYJOIN(ARRAYUNIQUE(values), ",")&"", LEN(ARRAYJOIN(ARRAYUNIQUE(values), ",")&"") - 1), 

This file has been truncated. show original

And yes - I’m keeping it as a Gist on GitHub for easy access. I’ll be adding more of these gems as I go.

[kuovonne · Accepted solution]

If there might be empty values, you could use
COUNTA(ARRAYUNIQUE(values))
instead of
COUNTALL(ARRAYUNIQUE(values))

The differences between COUNTA() and COUNTALL() are documented in the formula field reference. COUNTALL() counts all values, including blanks. COUNTA() does not count blanks.

[Sean_Lake1]

kuovonne:

COUNTALL(ARRAYUNIQUE(values))

Doesn’t work:
COUNTALL(ARRAYUNIQUE(values))
for

Rollup fields aren’t an option for other rollups, so in my case, trying to count the unique values in the rollup field won’t work. I have the above in a formula field and it only returns a 1.

[kuovonne]

You probably need a rollup of a lookup with ARRAYFLATTEN. It is hard to tell without a deeper dive into your schema.

[Megan_Snape2]

This is nice @Collin_Schwantes, I like the [^,]
For a rollup you’d need to convert it into a string and add the separators

IF(
  LEN(ARRAYJOIN(ARRAYUNIQUE(values),',')&"") > 0, 
  LEN(REGEX_REPLACE(ARRAYJOIN(ARRAYUNIQUE(values),',')&"","[^,]",""))+1,
0)

[Debbie_Benkovic]

Hi Russell,

This is the solution that I am looking for but it isn't returning the right number, I think because the values I'm trying to count have a comma in them. Would this be why and would you suggest how to fix? I am not a coder so modifying your code is difficult for me.

Debbie