Most frequent value within the filtered list

Hello,

I'm looking for a formula to show me the top 5 (or other top) most frequent text in a FILTERED list and next to it, to show the presence in % of this text in FILTERED list.
I'm currently using this formula but it only gives the most frequent.

=INDEX(Table4[SIRE NAME],MODE(IF(SUBTOTAL(3,OFFSET(H2,ROW(Table4[SIRE NAME])-ROW(H2),0)),MATCH(Table4[SIRE NAME],Table4[SIRE NAME],0)*{1,1})))

Thank you.

Please see attached the example.

In this example, I sort the list by age (AGE 1) and I find out that DRUMMER is most frequent name (20 times = 30% from 67 "AGE 1" units).
The second place is for SUHNSTAR and 3rd place for CARLO.
If we filter by AGE 2 and AGE 3 we will get other values.

I need, please, a formula to calculate all these automatically.
Thank you.

Originally Posted by MrShorty

Is it necessary to do this by formula? My first thought was to use a pivot table. I created a pivot table with Sire name as the row labels, Count of sire name for the values field, and age as the report filter. I click on the Sire name/row labels drop down, select value filters, top 10, where I can choose to return the top 5 count of sire name values. I can filter by age using the age drop down filter.

Something like this http://www.excel-easy.com/data-analy...ot-tables.html should help if you are unfamiliar with the mechanics of setting up a pivot table.

Thank you MrShorty
It is necessary to use a formula because I constantly add/remove rows from this table. The original table has 12.000 rows and more columns. I also use slices and I want this information to be in real time when I need it on different sheet.