Top 7 cccurrence in a range

Hello all,

I would like to find 7 numbers in a range which occurred the most. For example I attached a worksheet. I have a range of numbers (A1:G13). I would like to have a macro which will check which numbers occurred the most and then that numbers will be written in the bordered row (A16:G16). In the next row (A19:G19) I would like to see the occurrence count of the particular number.

Would you be so kind and help me with this macro?

I appreciate all your replies.
Thank you

Use this array formula

=INDEX(A$1:A$13,MATCH(MAX(COUNTIF(A$1:A$13,A$1:A$13)),COUNTIF(A$1:A$13,A$1:A$13),0))

and this no-array formula

=COUNTIF(A$1:A$13,A$16)

Hi Bob,

Thank you very much for your fast response. I tried to copy the first formula to cell A16 but it shows an error and return value "n/a". What I did wrong?

Originally Posted by Link

Hi Bob,

Thank you very much for your fast response. I tried to copy the first formula to cell A16 but it shows an error and return value "n/a". What I did wrong?

You didn't array-enter it.