(Solved) How do I increase this array formula?

Question

A reply on this thread (by "Mike H") lists how to name up to 3 of the most common text entries using these formulas:

"First

=INDEX(A1:A100,MODE(IF(A1:A100<>"",MATCH(A1:A100,A1:A100,0))))

Second

=INDEX(A1:A100,MODE(IF((A1:A100<>"")*(A1:A100<>INDEX(A1:A100,MODE(IF(A1:A100<>"",MATCH(A1:A100,A1:A100,0))))),MATCH(A1:A100,A1:A100,0))))

Third

=INDEX(A1:A100,MODE(IF(((A1:A100<>"")*(A1:A100<>INDEX(A1:A100,MODE(IF(A1:A100<>"",MATCH(A1:A100,A1:A100,0)))))*(A1:A100<>INDEX(A1:A100,MODE(IF((A1:A100<>"")*(A1:A100<>INDEX(A1:A100,MODE(IF(A1:A100<>"",MATCH(A1:A100,A1:A100,0))))),MATCH(A1:A100,A1:A100,0)))))),MATCH(A1:A100,A1:A100,0))))"

However, I want to have that reach up to 6 entries.

Could someone tell me what the formulas for the 4th, 5th and 6th would be?

Answer 1

See https://www.mrexcel.com/board/threads/how-to-find-5-most-frequent-non-numeric-values-in-a-range.182173/

Answer 2

This is just what I needed, and it's much more lightweight on my sheet than the previous one. Thank you!

Answer 3

I don't exactly understand your solution. I want cells to show the most frequent English letters in a given range.

My data has no numbers in it.

Answer 4

Why so complicated?

C2: =RANK.EQ([@Points],[Points],0)

Now you can filter the data by Rank less then 7 and get the result.

Or simply use the Top10 filter:

All you have to do is to format the data as table. And of course sort the data to get the result into an order.

Andreas.