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.
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,” “,””))+1Here, 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,” ”,””))+1First 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.
