Count Uniques from a Lookup

[Jason_Friedlan1]

I have a table that has multiple lookup fields to another table. I need to be able to sum the number of unique values in each lookup but keep failing to get it to work.

Example is I have a lookup called “Promotions” and then I want the next column to says # of Unique Promotions.

Please help!

[kuovonne]

Welcome back to the Airtable community forums!

You can use a rollup with the ARRAYUNIQUE(values) to return the number of unique values. Encase that value in the ARRAYJOIN() function to specify a unique character that will never appear in any of the values.

ARRAYJOIN(ARRAYUNIQUE(values), ", ")

Then use a formula field to calculate the number of values in the rollup, based on the unique character that you picked for the rollup.

IF({Rollup Field}, 
  LEN({Rollup Field}) - LEN(SUBSTITUTE({Rollup Field}, ",", "")) + 1
)

[Jason_Friedlan1]

I tried that and I am getting somewhere on some columns but columns that have much longer text and various unique characters for each lookup entry the resulting count formula doesn’t display accurate numbers

[kuovonne]

Are you including a unique character in the ARRAYJOIN that does not exist in any of the lookup values? In my example, I include a comma because that is a common character for separating items in a list. However, if any of your lookup values will contain a comma, the system will not work. Also, if any of your lookups have blank values, that will also cause problems. In that case you should make the rollup a conditional rollup to include only records that have a value to lookup.

If this information isn’t enough, can you please include screen shots with your sample data and formulas?

[Jason_Friedlan1]

I’d rather not post my data online is there a way to contact via email?