Most Frequent Value in a Column

Barry has a worksheet that contains readings from a production process at his plant. These readings are in column D of the worksheet, and there can be several thousand of them on a daily basis. The readings are always positive integer values. Barry needs to know what reading most frequently occurs in the column. He would like to know what the reading is and how many occurrences there are for that reading.

There are many different ways you can approach this task, but I'll consider the most straightforward. Historically, Excel has provided the MODE function, which returns the most frequently occurring value in a range of cells. So, Barry could return the most frequent value by simply using this formula:

=MODE(D:D)

What this doesn't do, however, is return "ties" where two values may occur at the same frequency. That is why, beginning with Excel 2010, Microsoft introduced the MODE.SNGL and MODE.MULT functions. The MODE.SNGL function returns a single value, so it is effectively the same as the MODE function. MODE.MULT, however, could return multiple values—in other words, ties:

=MODE.MULT(D:D)

Since MODE.MULT could return multiple results, it was necessary to enter it as an array formula. In the most modern versions of Excel, however, no special entry is needed. If you use the MODE.MULT formula in cell F2, Barry could determine the count he wanted by putting this in another cell:

=COUNTIF(D:D,F2)

If you are using Microsoft 365, you could devise a formula that will return all of the info that Barry needs—including ties:

=LET(n,MODE.MULT(D:.D),HSTACK(n,COUNTIF(D:.D,n)))

Note that this formula uses a trimmed version of column D, accessing only those cells that contain values. This is done by using the dot indicator before the second portion of the range. You could also use this variation of the formula if you find it easier to use:

=LET(r,TRIMRANGE(D:D),n,MODE.MULT(r),HSTACK(n,COUNTIF(r,n)))

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13974) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.