Count uniques in a field

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().)

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

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.

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.