List and Count the # of unique entries in a range

[Peptide]

Hello,
Looking for some help if possible. I tried to be as detailed as possible Any help would be great!

I have a range of data (B2:D4) that has various entries. I would like to find out how many unique entries there are and how many exist for each.

Data
B2 to D2 = Blank, Gray, Red
B3 to D3 = Red, Blank, Green
B4 to D4 = Blue, Green, Red

Would like to place the unique results starting in E3, then E4, E5 etc
Would like to place the # of occurrences starting in F3, then F4, F5 etc

Result (would like to exclude blank cells)

E3 Gray F3 1
E4 Red F4 3
E5 Green F5 2
E6 Blue F6 1
If no more found, all the next cells would be blank vs #N/A for example.

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​	E​	F​
1​
2​			Gray​	Red​
3​		Red​		Green​	Gray​	1​
4​		Blue​	Green​	Red​	Red​	3​
5​					Green​	2​
6​					Blue​	1​
7​

Array formula in E3 copied down
=IFERROR(INDIRECT(TEXT(SMALL(IF(B$2:D$4<>"",IF(COUNTIF(E$2:E2,B$2:D$4)=0,ROW(B$2:D$4)*10^5+COLUMN(B$2:D$4))),1),"R0C00000"),0),"")
confirmed with Ctrl+Shift+Enter, not just Enter

Formula in F3 copied down
=IF(E3="","",COUNTIF(B$2:D$4,E3))

Hope this helps

M.

 

[Peptide]

Marcelo - this is brilliant!!! Thank you sooo much!

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.