Count uniques in a field

#1

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.

#2

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