Help

Count Unique Values in Rollup

Solved
Jump to Solution
8456 13
cancel
Showing results for 
Search instead for 
Did you mean: 
Russell_Bishop1
7 - App Architect
7 - App Architect

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:

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.

13 Replies 13
kuovonne
18 - Pluto
18 - Pluto

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.

Doesn’t work:
COUNTALL(ARRAYUNIQUE(values))
for
image
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.

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

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)