Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Count Specific Words in a Column in Excel (2 Methods)

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.


Download the Practice Workbook

You can download the Excel file from the link below.


2 Methods to Count Specific Words in Column in Excel

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.

2 Methods to Count Specific Words in Column in Excel


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)

Combine SUMPRODUCT, LEN, and SUBSTITUTE Functions to Count Specific Words in a Column in Excel

  • Secondly, hit the Enter button.
  • Now you’ve got the count result for the column in that cell.

Excel Count Specific Words in Column

Formula Breakdown

  • To begin with, there are two parts in 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.

Read More: How to Count Words in Excel Column (5 Useful Ways)


Similar Readings


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)

Count Specific Words in Column Ignoring Case

  • 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.

Read More: How to Count Specific Names in Excel (3 Useful Methods)


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)

How to Count How Many Times A Word Appears in A Cell in Excel

  • 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.

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 about the letter cases. Leave any questions that might appear in your mind while going through the article; we will be responding asap.


Related Readings

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo