It’s possible that the instance of a word in a text string repeats frequently. You might also be interested in counting the total number of occurrences of specific words in your texts. If it’s so, we recommend you go through the following article carefully. This article will show you two distinct methods that you can use to count specific words in a column in Excel.
How to Count Specific Words in Column in Excel: 2 Methods
We’ll go over two practical methods to count specific words in an Excel column. But before that, let’s first understand the basics of counting specific words throughout a text line in Excel. Let’s take a text line first. For example, “Visit Exceldemy to Learn Excel”, where we will be counting the instances of the word “Exceldemy”. To do so,
- Firstly, count the total length of the text line first. Which is 30.
- Secondly, count the total length of the text line without the word “Exceldemy”. Which is 21.
- Thirdly, if we subtract the result of Step-1 and Step-2, we will find the length of the word “Exceldemy” which is 30-21=9.
- Fourthly, count the length of the word “Exceldemy” distinctly. Which is 9 again.
- Lastly, Let’s divide the result of Step-3 by the result of Step-4. We will get 1.
Thus, we can get the answer to our question. So, you now know how to determine how many times a given word appears within a text line. Moreover, here is a snapshot of the dataset for this article. We’ll count the two words – “happy” and “life” for the range C5:C10 in column C.
1. Combine Functions to Count Specific Words in Column
In this section, we will combine the SUMPRODUCT, LEN, and SUBSTITUTE functions to count the specific words in a column. Remember the formula is case sensitive and later we will show you another formula to count specific words in a column by ignoring the word cases.
Steps:
- Firstly, select cell D15 and type the following formula.
=SUMPRODUCT(LEN(C5:C10)-LEN(SUBSTITUTE(C5:C10,$C$12,"")))/LEN($C$12)+SUMPRODUCT(LEN(C5:C10)-LEN(SUBSTITUTE(C5:C10,$C$13,"")))/LEN($C$13)
- Secondly, hit the Enter button.
- Now you’ve got the count results for the column in that cell.
Formula Breakdown
- To begin with, there are two parts to the formula. Those parts are for word 1 and word 2 respectively.
- LEN(C5:C10)
- Output: {63;58;74;104;44;114}.
- This function returns the string lengths for the cells.
- LEN(SUBSTITUTE(C5:C10,$C$12,””))
- Output: {58;58;74;104;34;114}.
- The SUBSTITUTE function replaces all “happy” in the specific cell range. Then, the LEN function returns the length of this in an array.
- LEN($C$12)
- Output: 5.
- This returns the word 01 length.
- After that, the first part reduces to -> SUMPRODUCT({5;0;0;0;10;0})/5
- Output: 3.
- This counts the number of “happy” in the specified column. 5+10=15, then 15/5=3. Thus, we get the value.
- Similarly, the second part reduces to -> SUMPRODUCT({4;0;8;0;0;0})/4
- Output: 3.
- Finally, we get the output 3+3=6.
That’s it. Now let’s move on to the next method.
2. Count Specific Words in Column Ignoring Case
In the previous method, the formula was case-sensitive. In this section, we will implement the UPPER function to ignore cases. Additionally, you can use the LOWER function to do so too.
Steps:
- Firstly, type the following formula in cell D15.
=SUMPRODUCT(LEN(C5:C10)-LEN(SUBSTITUTE(UPPER(C5:C10),UPPER($C$12),"")))/LEN($C$12)+SUMPRODUCT(LEN(C5:C10)-LEN(SUBSTITUTE(UPPER(C5:C10),UPPER($C$13),"")))/LEN($C$13)
- Then, press Enter.
- The formula is similar to the first method. So, we will skip the formula breakdown here. We’ve used the UPPER function to match the cases, you also use similar functions.
How to Count How Many Times A Word Appears in A Cell in Excel
In this section, we will show you a formula to count the number of times a word appears in a cell in Excel. We will use a formula similar to the first method in this article. Moreover, we will use another dataset for this.
Steps:
- To begin with, type the following formula in cell D5.
=(LEN(B5:B10)-LEN(SUBSTITUTE(B5:B10,$C$12,"")))/LEN($C$12)
- Then, press Enter.
- We have already discussed how this formula works. This completes this section.
Things to Remember
- Select the range of the cells carefully.
- Don’t leave any space inside the double quotation marks.
- Keep the word intended to count inside double quotation marks.
Download the Practice Workbook
You can download the Excel file from the link below.
Conclusion
In this article, we have discussed two methods to count specific words in a column in Excel. The first method serves its purpose concerning the letter case, while the second method is blind to the letter cases. Leave any questions that might appear in your mind while going through the article; we will be responding ASAP.
<< Go Back to Count Words | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
