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

Solution

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: