Count Number of Times Values appear in a Column and Rank top 3

[uk747]

Hi I have Data below

What I need to do is count the values in Column A and Show the top 3 where column B has abc

In Example below In Column A Tom appears 4 times, **** 3, harry 2 and mary 6 however i only want it to count where column B is abc

So Tom is 1, **** is 3, harry is 2 and mary 0

so end result in F2 would be:
F G
**** 3
harry 2
tom 1

	A	B
1​	tom	abc
2​	****	abc
3​	harry	abc
4​	mary
5​	tom
6​	****	abc
7​	harry	abc
8​	mary
9​	mary
10​	mary
11​	mary
12​	mary
13​	tom
14​	tom
15​	****	abc

​

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	A	B
2	tom	abc		Rich	3
3	Rich	abc		harry	2
4	harry	abc		tom	1
5	mary
6	tom
7	Rich	abc
8	harry	abc
9	mary
10	mary
11	mary
12	mary
13	mary
14	tom
15	tom
16	Rich	abc
17
Sheet4

Cell Formulas
Range	Formula
D2:E4	D2	=LET(u,UNIQUE(FILTER(A2:A100,B2:B100="abc")),SORT(HSTACK(u,COUNTIFS(A:A,u,B:B,"abc")),2,-1))
Dynamic array formulas.

 

[uk747]

Thanks Fluff just noticed one thing when i went to proper data, if Column A has Blanks in it and Column B has abc it will show

Is it a quick fix for it to ignore where blanks in column A and only count the actual names

****	2​
tom	1​
harry	1​
0​	0​

 

[Fluff]

How about

=LET(u,UNIQUE(FILTER(A2:A100,(B2:B100="abc")*(A2:A100<>""))),SORT(HSTACK(u,COUNTIFS(A:A,u,B:B,"abc")),2,-1))

 

[uk747]

=LET(u,UNIQUE(FILTER(A2:A100,(B2:B100="abc")*(A2:A100<>""))),SORT(HSTACK(u,COUNTIFS(A:A,u,B:B,"abc")),2,-1))

Thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback.