 SOLVED

# Excel Formula

Occasional Contributor

# Excel Formula

Hi,

i want to list the 5 most frequented numbers in a column of my excel sheet top down. could anyone give me an idea about the right formula?

10 Replies
best response confirmed by BabakGhadiri (Occasional Contributor)
Solution

# Re: Excel Formula

Let's say the numbers are in A2:A101.

In C2, enter the formula

=MODE(IF(ISERROR(MATCH(\$A\$2:\$A\$101,\$C\$1:\$C1,0)),\$A\$2:\$A\$101))

and confirm it with Ctrl+Shift+Enter to turn it into an array formula (this is essential!)

Fill down to C6. See the attached sample workbook.

# Re: Excel Formula

Thanks,

but i do not know what is wrong with my sheet, it does not work on my sheet, instead it pops up an error. i sent the screen shot to you, could you please check it.

# Re: Excel Formula

Column D simply uses COUNTIF to return the frequency of the number in column C.

The formula in C2 calculates the most frequently occurring number, that in C3 the second most frequently occurring number etc. These formulas only refer to column A, not to column D.

# Re: Excel Formula

With a pivot table:

Numbers column in rows area and in values area. Change from Sum to Count.

Sort values column in descending order.

Set a Top10 filter for the Numbers column.

# Re: Excel Formula

And on 365

``````=LET(
freq,     FREQUENCY(numbers,numbers),
skipLast, INDEX(freq, SEQUENCE(ROWS(freq)-1)),
topN,     SEQUENCE(5),
topNumbers, INDEX(SORTBY(numbers,skipLast,-1),topN),
topFreq,  LARGE(SORT(skipLast,,-1),topN),
IF({1,0}, topNumbers, topFreq)
)``````

# Re: Excel Formula

With ties:

``````=LET(
unique, UNIQUE(numbers),
count, COUNTIFS(numbers,unique),
sort, SORTBY(IF({1.0},unique,count),count,-1),
sorted_count, INDEX(sort,0,2),
include, sorted_count>=LARGE(sorted_count;5),
FILTER(sort,include)
)``````

# Re: Excel Formula

Thank you a lot, this was useful.

Thanks

# Re: Excel Formula

Thanks a lot, it was useful.

# Re: Excel Formula

Exactly, if only correct misprints for English version

``````=LET(
unique, UNIQUE(numbers),
count, COUNTIFS(numbers,uniq),
sort, SORTBY(IF({1},unique,count),count,-1),
sorted_count, INDEX(sort,0,1),
include, sorted_count>=LARGE(sorted_count,5),
FILTER(sort,include)
)``````

and add frequency to the result