Finding the most popular numbers in a spreadsheat

[rogerdygas]

I have a spread sheat of 48 numbers (ranging from cells B2:G9)

I would like to find the 8 most frequently occuring numbers

Could somebody please give me a formula to get this result. I have looked on the net and havent found anything that works for me and I have also looked on thsi forum and havent found anything that will help.

Thanks for your help

 

[Andrew Poulsom]

Welcome to Mr Excel.

Try in H2:

=MODE(IF(COUNTIF(H$1:H1,B$2:G$9),"",B$2:G$9))

confirmed with Ctrl+Shift+Enter and copied down. The formula won't return a number if it only occurs once in the range.

 

[rogerdygas]

thanks for your quick reply

This formula gives me the first most reocuring number (the mode) how do I get the second, third, fourth etc most popular numbers?

 

[Andrew Poulsom]

Did you copy the formula down?

 

[rogerdygas]

No I didnt. I have just done it now and it works great thanks