# Re: Count Unique Values in Rollup

Solved
4454 0
cancel
Showing results for
Did you mean:
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
18 - Pluto

If there might be empty values, you could use
`COUNTA(ARRAYUNIQUE(values))`
`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.

8 - Airtable Astronomer

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.

18 - Pluto

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

4 - Data Explorer

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