SOLVED

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

Highlighted
New Contributor

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

4 Replies
Highlighted
Best Response confirmed by cmav99 (New Contributor)
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)))
Highlighted

Thanks for the reply Zack!

Highlighted

Thanks for the response Zack

Highlighted
You are very welcome. Glad to help.