which number repeats and how many times?

[excelNewbie22]

hi,
is there's a formula for finding which number in a range, say a1:a1000, repeat itself the most,
and how many times?

example: (the actual data contains numbers only but longer)
1
2
1
2
1
3
4
5
6
2
3
1
3
2
2
2
1

 

[excelNewbie22]

ekrause and etaf thank you,
fluff thanks yet again, working perfectly, and thanks for your patience too!

p.s
alan, thank you too

 

[alansidman]

An alternative is to use Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"#N/A", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"#N/A"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[Fluff]

Glad we could help & thanks for the feedback.