In Microsoft Excel, we work with a large amount of data. In a large datasheet, usually, we see some repeated words. It is tiresome to find out those repeated words manually. In this tutorial, we will discuss 11 methods on how to count repeated words in Excel with the proper illustration.

**Table of Contents**Expand

**How to Count Repeated Words in Excel: 11 Methods**

We have a dataset of some people with their favorite fruits. We are going to use this as the sample dataset all through the article.

**1. Count the Number of Repeated Words Using the COUNTIF Function**

**The COUNTIF function **counts something based on a given reference.

We’ll add two rows to the dataset for this method. Now, we will count how many times **Banana **is repeated in the dataset.

**Step 1:**

- Go to
**Cell C17**and put the formula below.

`=COUNTIF(C5:C14,C16)`

**Step 2:**

- Now, press
**Enter**.

The result is **3**, which means **Banana **is present or repeated two times in the selected range. This **COUNTIF **function is case-insensitive.

**Read More:** How to Ignore Blanks and Count Duplicates in Excel

**2. COUNTIFS Function to Count Duplicates in Excel**

**The COUNTIFS function** is used to apply multiple conditions through multiple ranges.

We will apply only a single condition using the **COUNTIFS **function.

**Step 1:**

- Go to
**Cell C17**. - Write the following formula.

`=COUNTIFS(C5:C14,C16)`

**Step 2:**

- Press the
**Enter**button.

Like the **COUNTIF **function, this function is also case-insensitive. Both of them can not detect the lower case data of **Cell C11**.

**Related Content: **How to Use COUNTIF Formula to Find Duplicates

**3. Excel SUMPRODUCT Function to Count Repeated Terms**

**The SUMPRODUCT function** provides the sum of products of a given range.

We will use this **SUMPRODUCT **function here to count the repeated terms of the selected range.

**Step 1:**

- Go to
**Cell C17**. - Put the formula below.

`=SUMPRODUCT(--(C16=C5:C14))`

**Step 2:**

- Hit the
**Enter**button.

This **SUMPRODUCT **function can not detect case differences like the previous methods.

**Read More: **How to Count Duplicate Values Only Once in Excel

**4. Count Case Sensitive Duplicates in Excel**

**The SUM function** adds values from a range of numbers given in the formula.

**The EXACT function** compares text strings with a reference. Returns **TRUE **if both are the same, **FALSE **otherwise.

We will use the combination of **SUM **and **EXACT **function to count the duplicates in this section.

**Step 1:**

- Enter
**Cell C17**. - Copy the formula below and paste it into that cell.

`=SUM(--EXACT(C5:C14,C16))`

**Step 2:**

- Now, hit
**Enter**.

The result is **2**. But in the previous methods, we get **3**. This is because the **EXACT **function is case-sensitive. Look at **Cell C11**, the first character is in lower case instead of upper case.

**Step 3:**

- Go to Cell
**C11**and modify the first character.

Now, the result is **3**.

**Related Content:** How to Count Duplicates with Pivot Table in Excel

**5. Use SUM, LEN, SUBSTITUTE Function to Count the Number of a Text String**

**The LEN function** counts the number of characters of a word.

**The SUBSTITUTE function** replaces a present word with a new word.

We will use the combination of **SUM, LEN, **and **SUBSTITUTE** functions to find and count the repeated words.

**Step 1:**

- Enter the following formula on
**Cell C17**.

`=SUM(LEN(C5:C14)-LEN(SUBSTITUTE(C5:C14,C16,"")))/LEN(C16)`

**Step 2:**

- Press the
**Enter**button.

This method is also case-sensitive.

We can also use the **SUMPRODUCT **function instead of the **SUM **function and will get the same result. The formula will be:

`=SUMPRODUCT(LEN(C5:C14)-LEN(SUBSTITUTE(C5:C14,C16,"")))/LEN(C16)`

**Formula Break****down**

**SUBSTITUTE(C5:C14,C16,””)**

It will replace the value of **C16** by **“”** from the range of **C5:C14**.

**Result: [ ,Apple, ,Pineapple, Grapes, Guava, banana, Guava, Apple, Apple]**

**=LEN(SUBSTITUTE(C5:C14,C16,””))**

This will count the length of each data after the **SUBSTITUTE** operation.

**Result:[0, 5, 0, 9, 6, 5, 6, 5, 5, 5]**

**LEN(C5:C14)**

This will count the length of each data from the range **C5:C14**.

**Result: [6, 5,6, 9, 6, 5, 6, 5, 5, 5]**

**LEN(C5:C14)-LEN(SUBSTITUTE(C5:C14,C16,””))**

Subtract operation is performed here.

**Result:[6, 0, 6, 0, 0, 0, 0, 0, 0, 0]**

**SUM(LEN(C5:C14)-LEN(SUBSTITUTE(C5:C14,C16,””)))**

Sum the subtracted result.

**Result: [12]**

**LEN(C16)**

Find the length of **Cell C16**.

**Result:[6]**

**SUM(LEN(C5:C14)-LEN(SUBSTITUTE(C5:C14,C16,””)))/LEN(C16)**

Divide the sum result by the length of **Cell C16**.

**Result:[2]**

**Read More: **VBA to Count Duplicates in Range in Excel

**6. Count the Order of Occurrence of Repeated Words in Excel**

We can count the orders of repeated words using the **COUNTIF** function. A combination of relative, absolute, and mixed cell references is used in the formula for this method.

We added a column named **Order **for this purpose.

**Step 1:**

- Go to
**Cell D5**. - Write the following formula.

`=COUNTIF($C$5:$C5,C5)`

**Step 2:**

- Now, hit the
**Enter**button and drag the**Fill Handle**icon.

Order is showing for each fruit. The maximum number of orders is the count of repeated times.

**Read More:** Excel VBA to Count Duplicates in a Column

**7. Count Duplicates without First Occurrence**

Sometimes we need to know how many times a word is repeated excluding the **1st** occurrences. Follow this method for this.

**Step 1:**

- Go to
**Cell C17**and put the following formula.

`=COUNTIF(C5:C14,C16)-1`

**Step 2:**

- Hit the
**Enter**button.

**8. Count All Repeated Words in a Column without 1st Occurrences**

**The IF function** is one of the most used functions of **Microsoft Excel**. This function offers to compare between different values according to our expectations.

We will combine the **IF **and **COUNTIF **function in this section.

We add one new column and a row in the dataset for this purpose.

**Step 1:**

- First, go to
**Cell D5**. - Put the following formula.

`=IF(COUNTIF($C$5:$C5,C5)>1,"Repeated"," ")`

**Step 2:**

- Now, hit
**Enter**and pull the**Fill Handle**icon.

This formula checks if there are any repeated values in the range **C5** to **C14**. And if found any repeated value, then indicate that from the **2nd** occurrences.

**Step 3:**

- Now, put a formula at
**Cell D16**to count the repetitions.

`=COUNTIF(D5:D14,"Repeated")`

**Step 4:**

- Hit the
**Enter**button.

Now, get the total repetitions without the **1st** occurrences in the selective range.

**9. ****Count the Total of Duplicates in a Column in Excel**

**The ROWS function** provides the number of rows from the given reference or array.

We will combine the **ROWS**, **IF, **and **COUNTIF **functions in this section.

**Step 1:**

- Write the following formula on
**Cell C16**.

`=ROWS($C$5:$C$14)-SUM(IF(COUNTIF(C5:$C$14,C5:$C$14)=1,1,0))`

**Step 2:**

- Hit the
**Enter**button now.

We get the total number of repeated words from the selected range with the 1st occurrence.

**Formula Breakdown**

**COUNTIF(C5:$C$14,C5:$C$14)**

This will count **C5:C14** from the range **C5:C14**.

**Result: [3,3,3,1,1,2,3,2,3,3]**

**IF(COUNTIF(C5:$C$14,C5:$C$14)=1,1,0)**

It will represent the result of **COUNTIF** through **1** and **0**. Those are **1** in **COUNTIF** function will be **1** and the rest will be **0**.

**Result: [0,0,0,1,1,0,0,0,0,0]**

**SUM(IF(COUNTIF(C5:$C$14,C5:$C$14)=1,1,0))**

It will sum the values of the **IF** function.

**Result: 2**

**ROWS($C$5:$C$14)**

It will show the number of rows from the range **C5:C14**

**Result: 10**

**ROWS($C$5:$C$14)-SUM(IF(COUNTIF(C5:$C$14,C5:$C$14)=1,1,0))**

This will subtract the value of the **SUM** function from the **ROWS** function.

**Result: 8**

**10. Combination of SUMPRODUCT and COUNTIF Functions to Get the Total Number of ****Repetitive ****Words**

We will simply combine the **SUMPRODUCT **and **COUNTIF **functions here to get the total number of repetitive words here.

**Step 1:**

- Go to
**Cell C16**. - Copy and paste the formula below.

`=SUMPRODUCT(--(COUNTIF(C5:C14,C5:C14)>1))`

**Step 2:**

- Hit the
**Enter**button finally.

We get the number of total repetitive words.

**11. VBA to Count the Total Number Repeated Words **

We will apply a VBA code to get the total number of repetitive words.

**Step 1:**

- Go to the
**Developer**tab first. - Click on
**Record Macro**and set a name for the macro and press**OK**.

**Step 2:**

- Now, press the
**Macros**option. - Select the desired macro and
**Step Into**it.

**Step 3:**

- Now, put the following code on the command module.

```
Sub Count_Repeated_Words()
Range("C16").Formula = "=SUMPRODUCT(--(COUNTIF(C5:C14,C5:C14)>1))"
End Sub
```

**Step 4:**

- Finally, press
**F5**to run the code.

Now, the mentioned formula is set on **Cell C16**. And we get the total number of repeated words.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

In this article, we discussed how to count repeated words in Excel. I hope this will satisfy your needs. Please give your suggestions in the comment box.

**<< Go Back to Count Duplicates in Excel | Duplicates in Excel | Learn Excel**