Count and Rank from Data with Category

Hi there. I'm trying to count the data and rank it for every category on the same table. The data is from the yellow table. Is there a formula to creat a result on column H (blue table) and column M (green table). Specifically to creat the end result like the green table and auto arrange the data based on the rank.
Here I attach a screenshot and the sample file.
Appreciate your help.
Thank you.

Screenshot 2023-07-05 125337.png
Example 1.xlsx