This post presents two methods for extracting all the different words from a list and counting the number of occurrences of each word. The spreadsheet, including full open-source VBA code, can be downloaded from:

CountWords.zip

As an example, I have generated a list of dates starting with the current date, then each successive date between 1 and 28 days later. I have then extracted the month from each date, preceded by the month number, so the results can be sorted from January to December:

The first method uses a User Defined Function (UDF) with a scripting dictionary to extract all the unique words, then count the number of occurrences of each. It also has an option to sort the results, either in alphabetical order, or by number of occurencies:

The main code for the UDF is shown below:

Function CountWords(WordList As Variant, Optional Sortby As Long = 0, Optional Order As Long = 1) As Variant
    Dim WordCount As Scripting.Dictionary
    Dim NumRows As Long, ItemVal As Long, sWord As Variant, RtnA() As Variant
    Dim i As Long, NumRes As Long, key As Variant
    
    ' Convert WordLIst to variant array
    WordList = WordList.Value2
    
    NumRows = UBound(WordList)
    
    ' Set up WordCount dictionary
    Set WordCount = New Scripting.Dictionary

    For i = 1 To NumRows
        sWord = WordList(i, 1)
        If sWord <> Empty Then
            If WordCount.Exists(key:=sWord) = False Then
                WordCount.Add sWord, 1
            Else
                ItemVal = WordCount.Item(sWord) + 1
                WordCount.Remove sWord
                WordCount.Add sWord, ItemVal
            End If
        End If
    Next i
    
        
    NumRes = WordCount.Count
    ReDim RtnA(1 To NumRes, 1 To 2)
    
    i = 1
    For Each key In WordCount.Keys:
        RtnA(i, 1) = key
        RtnA(i, 2) = WordCount.Item(key)
        i = i + 1
    Next
    
    If Sortby > 0 Then RtnA = SortV(RtnA, Sortby, Order)
    
    Set WordCount = Nothing
    CountWords = RtnA
End Function

Code for the SortV function is included in the download file.

Note that a reference to the scripting dictionary library must be enabled. In the VBA editor, select Tools-References, scroll down to Microsoft Scripting Runtime, and select.

The second option is to use the built-in UNIQUE function, available in recent releases of Excel, and then use COUNTIF to count the number of occurrences of each word.

For more examples using the VBA scripting dictionary, and links to detailed help, see:

Dictionary link and

All about dictionaries