# How to Count Specific Words in Excel (3 Effective Ways)

Microsoft Excel is a powerful software that uses Excel functions to count almost anything. Unfortunately, Excel doesn’t come with a built-in feature for word counts. Fortunately, you can create simple formulas to do practically any task by combining different functions. And we’ll apply this strategy to Excel’s word count. With this in mind, in this article, we will start from scratch and show you 3 simple methods to count specific words in Excel.

## 3 Suitable Ways to Count Specific Words in Excel

In this article, we will count certain words using the Excel formula, COUNTIF function, and VBA  code in Excel. Moreover, we will determine the total number of occurrences for sensitive and insensitive cases. Therefore, we will need separate formulas for separate cases. To demonstrate, we take a dataset that represents the geographical locations and their zip codes. ### 1. Apply Excel Formula to Count Certain Words

You can use a formula based on the SUBSTITUTE, LEN, and SUMPRODUCT functions to determine how many times a certain word (or any substring) appears inside a group of cells in Excel. The SUBSTITUTE function under Excel text function category generally replaces a text with another text in a text string. Alternatively, the LEN function returns an integer that represents the length of a text. Further, the SUMPRODUCT function returns the sum of products of any corresponding arrays. Moreover, we will calculate the sum products of 2 cases namely, case-insensitive & case-sensitive.

#### 1.1 Count Specific Words for Case-Insensitive

Since the SUBSTITUTE function is case-sensitive by default, as you are presumably aware, the word counting algorithm based on SUBSTITUTE is also case-sensitive. Follow the steps to implement.

Steps:

• First, in cell C12, we type New York as the input word. • This is because we want to count how many occurrences of New York happen in the dataset.
• Afterward, in cell C13, type the formula,
=SUMPRODUCT((LEN(B5:C10)-LEN(SUBSTITUTE(B5:C10,C12,””)))/LEN(C12))
• As a result, 3 appears as the return of the formula.
• Thus, the total number of New York occurrences is 3. 🔍 How Does the Formula Work?

• (SUBSTITUTE(B5:C10,C12,””): Here, the SUBSTITUTE function takes the substring (New York) out of the original text.
• (LEN(B5:C10)-LEN(SUBSTITUTE(B5:C10,C12,””)): while the LEN function determines the length of the text without the substring.
• SUMPRODUCT((LEN(B5:C10)-LEN(SUBSTITUTE(B5:C10,C12,””)))/LEN(C12)): Lastly, the SUMPRODUCT function adds up every element in the array to determine how many times the substring has appeared overall in the cell range.

#### 1.2 Count Specific Words for Case-Sensitive

For case-sensitive, use the UPPER function in the LEN function to change the case of the original text and the text you wish to count if you need to count both uppercase and lowercase instances of a specific term. The formula produces the same word count whether the term is inputted in uppercase, lowercase, or sentence case, as shown in the screenshot below. Follow the steps given below.

Steps:

• To begin with, we input Park in cell C12.
• After that, in cell C13, write the following formula,
=SUMPRODUCT((LEN(B5:C10)-LEN(SUBSTITUTE((UPPER(B5:C10)),UPPER(C12),””)))/LEN(C12))
• Further, tap the Enter key.
• Hence, integer 3 pops up.
• Therefore, the word Park appears 3 times in the dataset. ### 2. Use COUNTIF Function to Count Specific Words in Excel

In this method, we will use the COUNTIF function to count a certain word in our dataset. The COUNTIF function counts the list in a text string and returns an integer. Here, we will count the number of occurrences 1633 (zip code of New York) that appear in the dataset. To do so, follow the procedures carefully.

Steps:

• First, input 1633 in C12.
• See the below picture to understand better. • Next, in the C13, type the following COUNTIF formula,
=COUNTIF(B5:C10,C12)
• Later, press Enter.
• Subsequently, the total count appears in the dataset.
• Therefore, the total count of 1633 in the dataset is 2. ### 3. Run Excel VBA Code to Count Specific Words in Excel

In our last method, we will apply VBA code to count the specific words in a range. We will apply the WorksheetFunction property and .Countif VBA function. Moreover, we will include the MsgBox function to display the result. Follow the steps.

Steps:

• First, we input the word New York in C12, since we want to count the total occurrences of it. • Further, go to the Developer tab and click Visual Basic. • Consequently, the Visual Basic window pops up.
• Next, navigate through the Insert tab and tap the Module box. • Eventually, the module box appears.
• Later, type the VBA code in the box.
``````Sub Count_SWord()
Range("C13") = Application.WorksheetFunction.CountIf(Range("B5:C10"), Range("C12"))
MsgBox Range("C12") & " appears " & Range("C13") & " times."
End Sub```
```
• Finally, hit the green Run button. • Thus, the message box pops up and we obtain the desired result. ## Conclusion

In conclusion, we have discussed some easy steps to count specific words in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.

## Related Articles #### Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts  5 Excel Hacks You Never Knew  