+ Reply to Thread
Results 1 to 9 of 9

How to count the most frequent number in a Column

  1. #1
    Registered User
    Join Date
    04-06-2013
    Location
    USA
    MS-Off Ver
    MS Office Professional Plus 2019
    Posts
    77

    How to count the most frequent number in a Column

    Per Attached Sample Sheet, I need to find the most frequent number in each column B through AL and put the results in row 105 for each Column.

    I tried “Index(ai5:ai104,MODE(match(ai5:ai104,ai5:ai104,0)))” but that did not work.

    As always thanks for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: How to count the most frequent number in a Column

    From your description, wouldn't a simple MODE function work?

    That is, in B105:
    =MODE(B6:B104)

    If not, I recommend including the desired results, which you can enter manually, in your sample instead of the non-working formula.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: How to count the most frequent number in a Column

    Untested, but try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    =Index(ai5:ai104,MATCH(MODE(ai5:ai104),ai5:ai104,0))
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: How to count the most frequent number in a Column

    Is correct because most frequent in all these columns are empty cell.
    Use (into B105):

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    accept with CSE

    However you will get most of errors because most of cell are empty, like in col. B for 100 cells you got 2 numbers, once each.
    Just 4 columns indicate MOST frequent number.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  5. #5
    Registered User
    Join Date
    04-06-2013
    Location
    USA
    MS-Off Ver
    MS Office Professional Plus 2019
    Posts
    77

    Re: How to count the most frequent number in a Column

    63falcondude and TMS formula work just great.

    thank you for your help

    Cheers

    Nick

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: How to count the most frequent number in a Column

    Happy to help.

  7. #7
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: How to count the most frequent number in a Column

    What do You mean as
    Quote Originally Posted by Nickmsi View Post
    formula work just great.
    ?

    Przechwytywanie.JPG

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: How to count the most frequent number in a Column

    @KOKOSEK: I might be mistaken but, if that's your test data, it's a row of stuff ... but the formula is looking at a column.

    Not saying the formula is perfect but ... just saying

  9. #9
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: How to count the most frequent number in a Column

    That's results of formula (row 105, if you do not spot):
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    for all columns.
    I am just surprise why OP is so excited if results are like this.

    As I said in my first post (BTW, with other formula which gives absolutely the same results),
    "You will get most of errors because most of cell are empty, like in col. B for 100 cells you got 2 numbers, once each.
    Just 4 columns indicate MOST frequent number."

    Does not matter, that's my feelings. OP is happy, thread marked as SOLVED.
    So case closed

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. get most frequent number mode.mult give me same number
    By tomislav91 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-13-2017, 03:40 AM
  2. Frequent number for every category
    By Ankit_Kumar in forum Excel General
    Replies: 3
    Last Post: 04-27-2015, 06:15 AM
  3. Extracting most frequent number(s)?
    By figeroa in forum Excel General
    Replies: 4
    Last Post: 09-08-2014, 06:29 AM
  4. Check most frequent number in 1 column that has 4 digits number
    By Merson78 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-07-2014, 10:57 PM
  5. Replies: 3
    Last Post: 03-20-2012, 09:53 PM
  6. count most frequent value in column
    By runnerpaul in forum Excel General
    Replies: 6
    Last Post: 11-03-2006, 01:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1