Count uniques in a field

[Partner_Marketi]

I have a single select field, linked to another table of “deals”. I want to count the uniques of the single select per deal. When I “group” by this field I can change the summary bar to unique. Great!

That said… How can I get this into a field so I can filter by it? I tried to do a array unique but it doesn’t let me count by uniques. I also tried doing a lookup and the searching for commas in a separate formula field but that didn’t work either.

[W_Vann_Hall]

I’m not quite following the structure of your base. It sounds as if you have a number of [SomeSortOfRecord]s linked from the [Deal] table, and [SomeSortOfRecord] contains the {SingleSelect} field; you wish to know, across all [SomeSortOfRecord]s associated with a given [Deal], how many different values of {SingleSelect} have been selected. If this is the case, then it looks as if all you need to do is combine the two approaches you’ve already tried: Perform a rollup using ARRAYUNIQUE(), and count the number of commas in the resulting string. I think you can do this in a single rollup field by using an aggregation function:

IF(
    values,
    LEN(
        ARRAYUNIQUE(
            values
            )&','
        )-LEN(
            SUBSTITUTE(
                ARRAYUNIQUE(
                    values
                    ),
                ',',
                ''
                )
            )
    )

I say ‘think’ because I have a vague recollection of having something similar fail on me at some point — although I want to emphasize this was a while back and could very well have been a case of user error rather than a problem with Airtable. Just in case, though, should you get incorrect answers, try splitting it into two pieces: a simple rollup using the ARRAYUNIQUE() aggregation function and a separate formula field that handles the comma-counting routine. (If you get an #ERROR! response, try appending ‘&''’ to the instance of ARRAYUNIQUE(values)within the call to SUBSTITUTE().)

[Krisjanis_Dzalb]

Tried this but it seems that the LEN() function does not count the commas.

Tried the formula provided and ended up with 1 every time. (for the comma added in first LEN)

Also tried to make a Rollup Array unique field to fetch unique values from linked records field

Then tried the provided formula with Roll up field instead of ARRAYUNIQUE function but it turned out 1 every time

Tried parts separately and found out that first LEN and LEN with substitution put out number of symbols without commas.

[W_Vann_Hall]

First step is to figure out what the input to LEN() is — you want to make sure you’re actually getting a comma-separated string. Whether or not that’s the case depends on what type of fields you are rolling up, how you’re rolling up, etc.

If you just need a count of the number of values, use ARRAYJOIN(). If you need uniques, use ARRAYUNIQUE(), but you may need either to wrap ARRAYJOIN() around ARRAYUNIQUE() or append ‘&''’ to it to put it into a comma-separated string.

Once you are certain your input to LEN() is in the correct format, you can use the LEN() - SUBSTITUTE(LEN()) method to count the number of items.