- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 25, 2020 08:13 AM
I’ve had to do this quite a few times… my old solution was:
- Rollup with ARRAYUNIQUE(values)
- Remove any trailing ','s in another column
- 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.
Solved! Go to Solution.
Accepted Solutions
![kuovonne kuovonne](https://community.airtable.com/legacyfs/online/avatars/3X/b/c/bcecb2d58f8302e9d9f520621c02ff41be54488c.jpeg)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 10, 2022 09:04 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 27, 2020 09:32 PM
That’s awesome. How would you address the issue if some of the values being rolled up included a comma. In my example, a corporate name that included a comma (ie ABC Widgets, Inc.)?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 28, 2020 01:36 AM
Oooph! Very interesting. I suppose you’d have to work from the fact that Airtable will always use a ‘,’ separator…
Perhaps you could make a column called Safe Corporate Name that replaces ','s found in your Corporate Names and replaces them with another character using SUBSTITUTE() – and then you can use this Rollup formula on that field instead.
Hope that helps.
![JacobHands JacobHands](https://community.airtable.com/legacyfs/online/avatars/2X/b/b4c8ad235ef15808f0c4677a3d11c093bfa52ecc.png)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 01, 2021 01:19 PM
Hey thanks for this! It’s exactly what I needed.
![Breight_Group Breight_Group](https://community.airtable.com/legacyfs/online/avatars/3X/2/a/2a1c58ba0d25e512507a2e8369d68597f7a0086e.jpeg)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 14, 2022 06:38 AM
Just what we needed, thank you thank you
![kuovonne kuovonne](https://community.airtable.com/legacyfs/online/avatars/3X/b/c/bcecb2d58f8302e9d9f520621c02ff41be54488c.jpeg)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 14, 2022 06:49 AM
Or you could use …
COUNTALL(ARRAYUNIQUE(values))
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 17, 2022 06:25 AM
@kuovonne I was about to throw all of my papers in the air from my desk and shout from the rooftops “She’s solved it!”
However, I’m not sure these two are doing the same thing, but I don’t know why that is. Let me demonstrate:
Arrayunique just uses ARRAYUNIQUE(values) to list them.
Any thoughts? Is there a tweak to yours that would work?
![kuovonne kuovonne](https://community.airtable.com/legacyfs/online/avatars/3X/b/c/bcecb2d58f8302e9d9f520621c02ff41be54488c.jpeg)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 17, 2022 07:49 AM
That does look strange.
Are you rolling up the same target field from the other table?
Do you have any blanks in the field being rolled up? (I doubt it, or there would be two commas in a row.)
What is the field type of the field being rolled up?
![Collin_Schwante Collin_Schwante](https://community.airtable.com/legacyfs/online/avatars/2X/b/b4c8ad235ef15808f0c4677a3d11c093bfa52ecc.png)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 09, 2022 03:43 PM
Slightly more concise way of counting array items.
Where [Unique Editors] is a compacted, unique, joined array.
Assumes no commas in array values
IF(
LEN({Unique Editors}) > 0,
LEN(REGEX_REPLACE({Unique Editors},"[^,]",""))+1,
0)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 10, 2022 08:20 AM
Just returning to this – @kuovonne is 100% correct, the only addition is to add a condition in the rollup that the thing you’re looking for unique values of is not empty.
I am going to edit my post to make sure no one wastes their time on my awful, awful alternative :grinning_face_with_big_eyes:
![](/skins/images/75AB00B4920FD2D67A8CABF77C9DECC4/responsive_peak/images/icon_anonymous_message.png)