Help

Count Uniques from a Lookup

Topic Labels: Formulas
2896 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Jason_Friedlan1
6 - Interface Innovator
6 - Interface Innovator

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!

4 Replies 4

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
)

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

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?

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