How to Count Repeated Words in Excel (11 Methods)

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.

Download Practice Workbook

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

11 Methods to Count Repeated Words in Excel

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.

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 Count Duplicates in Column in Excel (3 Ways)

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.

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 Count Duplicate Values Only Once in Excel (3 Ways)

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.

Step 2:

• Hit the Enter button.

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

Read More: How to Count Duplicate Rows in Excel (4 Methods)

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.

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 Based on Multiple Criteria 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.

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:

Formula Breakdown

• 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: How to Count Words in Excel with Formula (2 Handy Examples)

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.

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: Count the Order of Occurrence of Duplicates in Excel (4 Methods)

Similar Readings

• How to Count Specific Words in a Column in Excel (2 Methods)
• Count Duplicates in Excel Pivot Table (2 Easy Ways)
• How to Count Duplicate Values in Multiple Columns in Excel (6 Ways)
• Excel Count Occurrences of Character in String

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.

Step 2:

• Hit the Enter button.

Related Content: How to Count Occurrences Per Day in Excel (4 Quick Ways)

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.

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.

Step 4:

• Hit the Enter button.

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

Read More: Excel Count Number of Occurrences of Each Value in a Column

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.

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

Read More: How to Ignore Blanks and Count Duplicates in Excel (3 Ways)

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.

Step 2:

• Hit the Enter button finally.

We get the number of total repetitive words.

Read More: Excel Formula to Count Specific Words in a Cell (3 Examples)

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.

Read More: Excel VBA to Count Duplicates in a Column (A Complete Analysis)

Conclusion

In this article, we discussed how to count repeated words in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Related Articles

• Count Duplicates in Two Columns in Excel (8 Methods)
• How to Count Specific Names in Excel (3 Useful Methods)
• VBA to Count Duplicates in Range in Excel (4 Methods)
• How to Count Words in Excel Column (5 Useful Ways)