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.

**Table of Contents**hide

## 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**.

**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 result for the column in that cell.

**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**

**COUNTIF Cell That Contains a Specific Text in Excel (Case-Sensitive and Insensitive)****How Do I Count Cells with Text in Excel (5 Methods)****Count If Cell Contains Text in Excel (5 Easy Approaches)**

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

**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)`

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