Most Frequent Word In A Range, Ignoring Blanks

Question

I currently use the following formula to find the most common word or number in a range:

=INDEX(E9:E18,MODE(MATCH(E9:E18,E9:E18,0)))

However, if there are any blank cells then the formula returns a blank cell as the mode. How can I modify this to find the most common word/number ignoring any blank cells?

Many thanks

Answer 1

Try the following User Defined Function:

Public Function MostFreq(rIn As Range) As Variant
    Dim c As Collection, r As Range, N As Long, How()
    Dim cc As Long, wf As WorksheetFunction
    Dim i As Long, Biggest As Long
    Set c = New Collection
    Set wf = Application.WorksheetFunction

    On Error Resume Next
    For Each r In rIn
        v = r.Text
        If v <> "" Then
        c.Add v, CStr(v)
        End If
    Next r

    On Error GoTo 0
    cc = c.Count
    ReDim How(1 To cc)

    For i = 1 To cc
        How(i) = wf.CountIf(rIn, c.Item(i))
    Next i
    Biggest = wf.Max(How)
    For i = 1 To cc
        If How(i) = Biggest Then
            MostFreq = c.Item(i)
        End If
    Next i
End Function

To avoid VBA, pick a cell (say A1) and enter the array formula:

=INDEX(E9:E18,MODE(IF((E9:E18<>"")*ISNA(MATCH(E9:E18,$B$1:$B1,0)),MATCH(E9:E18,E9:E18,0))))

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Here is an example:

Answer 2

I had the same question, but I had the additional issue of needing to accommodate ranges that could have 0, 1, or 2 values. The formula in this other answer to this question, as well as all of the formulas in this related question, resulted in #N/A errors whenever there were less than three values.

However this formula resolved that issue:

=INDIRECT(TEXT(MIN(IF(COUNTIF(Rng,Rng)=MAX(COUNTIF(Rng,Rng)),10^5*ROW(Rng)+COLUMN(Rng))),"R0C00000"),0)

Source. The source says it needs to be entered as an array formula with control + shift + enter, but it also worked without that for my ranges, which were all single columns.

If there are no values in the range, the formula above produces a zero when I need a blank, so I modified it as follows:

=IF(
    INDIRECT(TEXT(MIN(IF(COUNTIF(rng,rng)=MAX(COUNTIF(rng,rng)),10^5*ROW(rng)+COLUMN(rng))),"R0C00000"),0)=0,
    "",
    INDIRECT(TEXT(MIN(IF(COUNTIF(rng,rng)=MAX(COUNTIF(rng,rng)),10^5*ROW(rng)+COLUMN(rng))),"R0C00000"),0)
)

The INDIRECT formula above produces a cell reference, so the formula will not work outside of the sheet in which the range is located. If you'd like to use this in, say, Sheet2 when the range is in Sheet1, you'd have to add a reference back to the sheet where the range is:

=IF(
    INDIRECT("Sheet1!"&TEXT(MIN(IF(COUNTIF(rng,rng)=MAX(COUNTIF(rng,rng)),10^5*ROW(rng)+COLUMN(rng))),"R0C00000"),0)=0,
    "",
    INDIRECT("Sheet1!"&TEXT(MIN(IF(COUNTIF(rng,rng)=MAX(COUNTIF(rng,rng)),10^5*ROW(rng)+COLUMN(rng))),"R0C00000"),0)
)