In our practical life, we need to tally words according to different bases and ranges. By using Excel, we can easily tally words in different arguments. If we study how to tally words in Excel, we’ll learn different methods to tally words which will help us effectively in our practical life. In this article, we’ll try to discuss different methods on how to tally words in Excel.

**Table of Contents**hide

## How to Tally Words in Excel: 4 Useful Methods

Though Microsoft Excel doesn’t offer many built-in functions to tally words, we can combine various functions to tally words in Excel. In this article, we have shown various combinations of functions that we can also use to **count words** only. As Count and Tally have similarities except for some statistical works, we have used these formulas. For doing this, we have made a dataset of a **List of Best Seller Books with Product ID**. The dataset is like this.

Now, we’ll see how we can tally words on different bases with the help of different combined functions.

### 1. Applying LEN, TRIM, and SUBSTITUTE Functions to Tally Words in a Cell

Suppose, we want to tally or count words of **The Black Swan** in the **C5** cell. We can combine **LEN****, ****TRIM****, **and **SUBSTITUTE** functions while tallying words in a cell. In the** D5** cell, we can write the formula like this.

`=LEN(TRIM(C5))-LEN(SUBSTITUTE(C5,” “,””))+1`

Here, the **SUBSTITUTE** function replaces all of the spaces in the text in the **C5** cell.

Eventually, the **LEN** function then returns the total length of the text, including any spaces.

Thirdly, we have to add **1** to the total word count by subtracting the length of the text without any spaces from the entire length of the text.

Besides, the **TRIM** function clears the cell of any unwanted gaps.

Finally, by pressing **ENTER**, we find the output as **3**.

Lastly, we can use **Fill Handle** by dragging the **D5** cell downwards to get **Tallied Words** from cell **C6** cell to** the C13** cell.

As a result, we get the output like this.

**Read More: **How to Make a Tally Chart in Excel

### 2. Utilizing SUMPRODUCT, LEN, and SUBSTITUTE Functions to Tally Specific Word

When we need to tally a specific word in a column or row, we need to apply the combination of **SUMPRODUCT**, **LEN, **and **SUBSTITUTE **functions. We can write the formula for the **F5 **cell like this.

`=SUMPRODUCT((LEN(C5:C13)-LEN(SUBSTITUTE(C5:C13,F4,"")))/LEN(F4))`

Here, the **F4** cell refers to the specific word which we are willing to tally from cell **C5** to **C13.**

There is a tweak at the very end, similar to the prior formula. The formula has calculated the number of times the word or text which appears in the column by dividing the total number of words returned by the number of words in the text.

Similarly as before, if we press **ENTER**, we find the following output as **2**.

**Read More:** How to Make a Tally Sheet in Excel

### 3. Using SUMPRODUCT, LEN, and TRIM Functions to Tally All Words in a Column

If we want to tally the total words in a column we need to apply the combination of **SUMPRODUCT**, **LEN,** and **TRIM** functions. In the below picture, we want to tally words from cell **C5 **to cell** C13**. We can write the formula like this.

`=SUMPRODUCT(LEN(TRIM(C5:C13))-LEN(SUBSTITUTE(C5:C13,” ”,””))+1`

First and foremost, this formula is comparable to the previous technique because it employs the same functions; however, the final step differs slightly.

We have put the arguments within the** SUMPRODUCT** function, which aggregates all of the word tallies in the range** C5:C13** and displays the overall count in the **E5** cell.

Additionally, we need to remember to press **CTRL + SHIFT + ENTER** as well especially if you’re not a Microsft 365 user.

Eventually, by pressing **ENTER**, we get the output as **29**.

**Read More: **How to Tally a Column in Excel

### 4. Utilizing FREQUENCY Function to Tally Words in Ranges

In this method, we’re going to do an amazing task. Let’s say, you have some data points and now you want to compute the frequency with some specified range. If we need to tally words in different ranges by reordering them, we need to apply the **FREQUENCY** function. For doing this we have made a new dataset showing the **Marks Obtained** by the** Students. **The dataset is like this.

Here, **Range** refers to within which range we need to place the marks. **Bin** is basically the final value or a single word presentation of **Range**. For example, for a **Range 0-25**, we can write the **Bin **as** 25 **because **25** is the final value of that** Range**. We can then write the formula in the **G5 **cell like this.

`=FREQUENCY(C5:C15,F5:F7)`

Here, **C5:C15** refers to the** Marks** that the students’ have obtained.** F5:F7 **refers to the **Bin** which basically is a decider of the placement of the **Marks**.

And after placing the formula, finally, if we press **ENTER**, we’ll get the output of **Tallied Words** from cells **G5** to** G8**.

## Things to Remember

- The most important thing is that we can use only the
**FREQUENCY**function to tally words purely. - But we can apply purely the combination of
**LEN, TRIM, SUBSTITUTE,**and**SUMPRODUCT**functions when we need to count words only without**Tallying**them, but here we have used all of these functions taking count or tally as the same.

**Download Practice Workbook**

## Conclusion

By studying this article, we’ll be now able to tally words effectively in every practical sector. If you need any query please follow our official Excel online course platform **Exceldemy**.