Calculating the number of unique vales in a field in a linked record

This is how you can calculate the number of unique values in a field in a linked record. I couldn’t find a previous post on this when I was looking for help (apologies if I just missed it) so I thought I’d put one here for others to find.

Short answer: you can create a Rollup field specifying the linked table and the field you want to count unique vales in, then define an aggregation formula as follows: COUNTA(ARRAYUNIQUE(values))

The tool tips for the aggregation formula don’t make it clear that you can do this. It may appear that you are limited to the simple formulas they suggest, but this is not the case. I haven’t tested it to see how far you can go, but nesting ARRAYUNIQUE() within COUNTA() definitely works.

What is this used for?

In my case, I have a table of Locations where we have equipment (e.g. Chicago Office, London Office, etc.), and I have a list of Assets that tracks all the individual pieces of equipment. I also have an Asset Type table that tracks the basic information about the various types of assets we have (e.g. a particular model of printer) so we aren’t replicating that data for individual assets in the Asset table.

In the Asset Type table I wanted a count of the number of locations that that model of equipment was deployed in. That is, “OK, we have 42 of that model of printer. How many locations do we have them in?”

The Asset Type table already had a link to Assets, so in the Asset Type table I created a new Rollup field called ‘Total Locations’ which specified the Asset table, the Location field in that table, and then used the formula shown above.