Help

Re: Count Unique Values in Rollup

Solved
Jump to Solution
7848 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Russell_Bishop1
8 - Airtable Astronomer
8 - Airtable Astronomer

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.

14 Replies 14
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)
Debbie_Benkovic
4 - Data Explorer
4 - Data Explorer

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