+22I’ve had to do this quite a few times… my old solution was:
Now I do it all inside the rollup with the following snippet:
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.
Best answer by kuovonne
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.
+15That’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.)?
+22That’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.)?
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.
+6Just what we needed, thank you thank you
+29Or you could use …
COUNTALL(ARRAYUNIQUE(values))
@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?
+29@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?
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?
+5Slightly 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)
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:
+29If 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.
+20Or you could use …
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.
+29You probably need a rollup of a lookup with ARRAYFLATTEN. It is hard to tell without a deeper dive into your schema.
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)
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)
+4Hi 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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.