How to Tally Words in Excel (with Formulas & VBA)

Tally means to count the number of items. So tallying words simply means counting the words. Although there is no direct way to count words, you can combine Excel functions, VBA code, etc to count words easily.

In the following dataset, we have the product name in Column A, the country of origin in Column B, and the product description in Column C. We will tally the words in the product description cell by combining the LEN, TRIM, and SUBSTITUTE functions. This is handy for a quick tally of the words in a cell.

➤ Select the output cell (D2) and type the following formula:

Or, If you’re using Excel 365, then use this formula to tally words:

Dealing with Empty Cells

The previous formula will return 1 for empty cells. So we can use an IF statement to check for blank cells.

➤ Use this formula to handle empty cells

To tally the words in a range of cells we can wrap the previous formula with the SUM or the SUMPRODUCT function. This will give the total count of the words in the selected cell range.

➤ Select the output cell (B8) and type the formula. For Excel 2019 or earlier versions, press the  Ctrl+Shift+Enter  keys. On Excel 365 or later versions just press  Enter .

To tally the number of times a certain word or text has appeared in a cell we can use the LEN and SUBSTITUTE functions. For example: I want to tally the occurrence of the word “and” in cell C2.

➤ Select the cell D2 and enter the formula:

Case-Insensitive

If you want to count both the lowercase and uppercase of the word then use the LOWER and UPPER functions respectively.

➤ Select the cell D2 and enter the formula:

For tallying specific words in a range of cells we can use a similar approach i.e. placing the formula inside the SUM or the SUMPRODUCT functions.

➤ Select cell B8 and type the formula. Press  Ctrl+Shift+Enter  to apply the array formula.

You can use a simple VBA macro to make a custom function for tallying words. This helps you avoid complex formulas and you can use it like other Excel functions.
Steps:

➤ Go to the Developer tab and click the Visual Basic option.

➤ Click on Insert and select Module.

➤ Copy the VBA code and paste it into the Module window.

Function TallyWords(rng As Range) As Long
    Dim cell As Range
    Dim wordCount As Long
    Dim words As Variant
    
    For Each cell In rng
        words = Split(Trim(cell.Value), " ")
        wordCount = wordCount + UBound(words) + 1
    Next cell
    
    TallyWords = wordCount
End Function

➤ Go back to your Excel sheet and type the custom function name in cell B8.

FAQ

How do I tally letters in a cell in Excel?

To tally letters, use =LEN(cell)-LEN(SUBSTITUTE(cell,”e”,””)). Replace the cell with the cell containing the letter and change the letter “e” with the letter of your choice.

How do I tally letters in a range of cells in Excel?

Use =SUM(LEN(range)-LEN(SUBSTITUTE(range,”e”,””))). Replace the range with the range you want to search the letter and change the letter “e” with the letter of your choice.

How to tally names in Excel?

Use =COUNTIF(range, “name”). Replace the range with the range containing the name and change the name with the name you want to search.

How to specify delimiters when tallying words?

Use =COUNTA(TEXTSPLIT(TRIM(cell),{” “,”-“},,1)). This formula can tally words separated by spaces as well as hyphens, just change cell with the cell containing the words.

Wrapping Up

In this tutorial, we’ve learned several ways to tally words in Excel. We looked at how to tally words in a single cell and how to handle empty cells. Then, we tallied words for a range of cells and also learned to tally specific words within a cell and across a range. Finally, we applied a simple VBA macro to create a word-counting function. Feel free to download the practice file and let us know which method you like the most.