SOLVED

Excel Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2375405%22%20slang%3D%22en-US%22%3EExcel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375405%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Ei%20want%20to%20list%20the%205%20most%20frequented%20numbers%20in%20a%20column%20of%20my%20excel%20sheet%20top%20down.%20could%20anyone%20give%20me%20an%20idea%20about%20the%20right%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2375405%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375677%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375677%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059919%22%20target%3D%22_blank%22%3E%40BabakGhadiri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20numbers%20are%20in%20A2%3AA101.%3C%2FP%3E%0A%3CP%3EIn%20C2%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMODE(IF(ISERROR(MATCH(%24A%242%3A%24A%24101%2C%24C%241%3A%24C1%2C0))%2C%24A%242%3A%24A%24101))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20confirm%20it%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%20to%20turn%20it%20into%20an%20%3CSTRONG%3Earray%20formula%3C%2FSTRONG%3E%20(this%20is%20essential!)%3C%2FP%3E%0A%3CP%3EFill%20down%20to%20C6.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0430.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282649iC9D8CFCB1F102F22%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0430.png%22%20alt%3D%22S0430.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20sample%20workbook.%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375735%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375735%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20but%20just%20a%20question%20%3A%3C%2FP%3E%3CP%3Ehow%20could%20this%20formula%20recognize%20the%20column%20%22D%22%20for%20frequency%2C%20in%20your%20formula%20is%20not%20mentioned%20any%20order%20for%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375853%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375853%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059919%22%20target%3D%22_blank%22%3E%40BabakGhadiri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EColumn%20D%20simply%20uses%20COUNTIF%20to%20return%20the%20frequency%20of%20the%20number%20in%20column%20C.%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20C2%20calculates%20the%20most%20frequently%20occurring%20number%2C%20that%20in%20C3%20the%20second%20most%20frequently%20occurring%20number%20etc.%20These%20formulas%20only%20refer%20to%20column%20A%2C%20not%20to%20column%20D.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375885%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375885%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059919%22%20target%3D%22_blank%22%3E%40BabakGhadiri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20a%20pivot%20table%3A%3C%2FP%3E%3CP%3ENumbers%20column%20in%20rows%20area%20and%20in%20values%20area.%20Change%20from%20Sum%20to%20Count.%3C%2FP%3E%3CP%3ESort%20values%20column%20in%20descending%20order.%3C%2FP%3E%3CP%3ESet%20a%20Top10%20filter%20for%20the%20Numbers%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375890%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375890%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059919%22%20target%3D%22_blank%22%3E%40BabakGhadiri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20on%20365%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0A%20%20freq%2C%20%20%20%20%20FREQUENCY(numbers%2Cnumbers)%2C%0A%20%20skipLast%2C%20INDEX(freq%2C%20SEQUENCE(ROWS(freq)-1))%2C%0A%20%20topN%2C%20%20%20%20%20SEQUENCE(5)%2C%0A%20%20topNumbers%2C%20INDEX(SORTBY(numbers%2CskipLast%2C-1)%2CtopN)%2C%0A%20%20topFreq%2C%20%20LARGE(SORT(skipLast%2C%2C-1)%2CtopN)%2C%0A%20IF(%7B1%2C0%7D%2C%20topNumbers%2C%20topFreq)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376077%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376077%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20ties%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0A%20%20unique%2C%20UNIQUE(numbers)%2C%0A%20%20count%2C%20COUNTIFS(numbers%2Cunique)%2C%0A%20%20sort%2C%20SORTBY(IF(%7B1.0%7D%2Cunique%2Ccount)%2Ccount%2C-1)%2C%0A%20%20sorted_count%2C%20INDEX(sort%2C0%2C2)%2C%0A%20%20include%2C%20sorted_count%26gt%3B%3DLARGE(sorted_count%3B5)%2C%0A%20%20FILTER(sort%2Cinclude)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376171%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376171%22%20slang%3D%22en-US%22%3EThank%20you%20a%20lot%2C%20this%20was%20useful.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376172%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376172%22%20slang%3D%22en-US%22%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376173%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376173%22%20slang%3D%22en-US%22%3EThanks%20a%20lot%2C%20it%20was%20useful.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376411%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376411%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExactly%2C%20if%20only%20correct%20misprints%20for%20English%20version%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0A%20%20unique%2C%20UNIQUE(numbers)%2C%0A%20%20count%2C%20COUNTIFS(numbers%2Cuniq)%2C%0A%20%20sort%2C%20SORTBY(IF(%7B1%7D%2Cunique%2Ccount)%2Ccount%2C-1)%2C%0A%20%20sorted_count%2C%20INDEX(sort%2C0%2C1)%2C%0A%20%20include%2C%20sorted_count%26gt%3B%3DLARGE(sorted_count%2C5)%2C%0A%20%20FILTER(sort%2Cinclude)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20add%20frequency%20to%20the%20result%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2618810%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2618810%22%20slang%3D%22en-US%22%3EHi%20Hans%20!%3CBR%20%2F%3Eyou%20sent%20me%20already%20a%20formula%20about%20the%205%20Top%20most%20repeated%20numbers%20in%20a%20column%20in%20excel.%20This%20was%20very%20useful%20for%20me.%20Just%20another%20question.%20when%20i%20fill%20down%20the%20column%20C%20%2C%20it%20shows%20till%20Row%2011%20and%20afterwards%20shows%20%23N%2FA%20.%20how%20can%20i%20make%20it%20show%20the%20rest%20numbers%20down%20without%20limit%20%3F%20or%20may%20be%20you%20send%20me%20a%20new%20formula%20without%20this%20limit%3F%3CBR%20%2F%3EThanks%20already%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2619267%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2619267%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059919%22%20target%3D%22_blank%22%3E%40BabakGhadiri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChange%20the%20formula%20to%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(MODE(IF(ISERROR(MATCH(%24A%242%3A%24A%24101%2C%24C%241%3A%24C1%2C0))%2C%24A%242%3A%24A%24101))%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2619910%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2619910%22%20slang%3D%22en-US%22%3EHi%20Sergei%20!%3CBR%20%2F%3Eyou%20sent%20me%20already%20a%20formula%20about%20the%205%20Top%20most%20repeated%20numbers%20in%20a%20column%20in%20excel.%20This%20was%20very%20useful%20for%20me.%20Just%20another%20question.%20when%20i%20fill%20down%20the%20column%20C%20%2C%20it%20shows%20till%20Row%2011%20and%20afterwards%20shows%20%23N%2FA%20.%20how%20can%20i%20make%20it%20show%20the%20rest%20numbers%20down%20without%20limit%20%3F%20or%20may%20be%20you%20send%20me%20a%20new%20formula%20without%20this%20limit%3F%3CBR%20%2F%3EThanks%20already%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2619932%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2619932%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3Ecould%20you%20please%20send%20to%20me%20its%20workbook%20as%20xslx%20file%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2620012%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2620012%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059919%22%20target%3D%22_blank%22%3E%40BabakGhadiri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20it%20is%2C%20although%20it%20shouldn't%20be%20hard%20to%20copy%2Fpaste%20the%20formula%20yourself...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2620869%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2620869%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059919%22%20target%3D%22_blank%22%3E%40BabakGhadiri%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20define%20%3CSTRONG%3Enumbers%3C%2FSTRONG%3E%20as%20dynamic%20range%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20427px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301103iEE32D1D1FCAEC6BD%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20check%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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?

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

@BabakGhadiri 

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.

 

S0430.png

 

See the attached sample workbook.

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.

@BabakGhadiri 

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.

@BabakGhadiri 

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.

 

 

@BabakGhadiri 

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)
)

@Sergei Baklan 

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)
)
Thank you a lot, this was useful.
Thanks
Thanks a lot, it was useful.

@Detlef Lewin 

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

 

Hi Hans !
you sent me already a formula about the 5 Top most repeated numbers in a column in excel. This was very useful for me. Just another question. when i fill down the column C , it shows till Row 11 and afterwards shows #N/A . how can i make it show the rest numbers down without limit ? or may be you send me a new formula without this limit?
Thanks already

@BabakGhadiri 

Change the formula to

 

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

Hi Sergei !
you sent me already a formula about the 5 Top most repeated numbers in a column in excel. This was very useful for me. Just another question. when i fill down the column C , it shows till Row 11 and afterwards shows #N/A . how can i make it show the rest numbers down without limit ? or may be you send me a new formula without this limit?
Thanks already
Hi,
could you please send to me its workbook as xslx file?

Thanks

@BabakGhadiri 

Here it is, although it shouldn't be hard to copy/paste the formula yourself...

@BabakGhadiri 

You may define numbers as dynamic range

image.png

Please check attached.

Thanks a lot !
actually i did it copy and paste on my sheet but it did not work. may be i need change something at settings.
Thank you again
Thanks a lot.