How to get the count of unique values from a list of repeated unique values

Maxwell L1200

I have a list of people lets say Jon Sam Rick each on of them is assigned to a colour lets say any combination of colours for something that looks something like this ( see screenshot). How can I count the number individual colours for each person. For example Jon has a combination of 5 red and 5 green so his count should be 2 for 2 distinct colours. I have tried count(distinct(collect( and that did not quite work I have tried count(distinct(vlookup and that also did not quite work. Could I add all unique values associated with one person to a list then count the values in that list? What is the best way to do this.

Screenshot 2025-06-03 12.39.46 PM.png

Paul Newcome

It would be something along the lines of:

=COUNT(DISTINCT(COLLECT({Color Column}, {Name Column}, @cell = "Jon")))

Paul Newcome

It would be something along the lines of:

=COUNT(DISTINCT(COLLECT({Color Column}, {Name Column}, @cell = "Jon")))