Count unique values from one column based on value in other column

[cmav99]

Hi,

I have 2 tabs in my excel sheet.
Tab 2 contains this data:

ID      COLOUR

28	Red		
28	Red	
28	Red
28	Blue		
28	Yellow		
28	Grey		
28	Grey		
28	Green		
28	Green		
28	Green		
28	Green		
15	Blue		
15	Yellow		
15	Yellow		
15	Yellow

In tab one I have this data:

ID   COLOUR_COUNT

28   empty
15   empty

I need to count the number of unique colours for each ID from tab 2 and add them to column 2 of my first tab so my tab one will look like this...

ID   COLOUR_COUNT

28   5
15   2

How can i do this?
Many thanks in advance for your input

[#2 · Accepted solution]

Hi there,

If you are using a Microsoft 365 subscription for Office, you can use this formula:

=COUNTA(UNIQUE(FILTER('Tab 2'!$B$2:$B$16,'Tab 2'!$A$2:$A$16=A2)))

[#3]

Thanks for the reply Zack!

[#4]

Thanks for the response Zack

[#5]

You are very welcome. Glad to help. :)