How to Count Repeated Words in Excel (11 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.


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)

Count the Number of Repeated Words Using the COUNTIF Function

Step 2:

  • Now, press Enter.

Count the Number of Repeated Words Using the COUNTIF Function

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)

COUNTIFS Function to Count Duplicates in Excel

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

Excel SUMPRODUCT Function to Count Repeated Terms

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

Count Case Sensitive Duplicates in Excel

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.

Count Case Sensitive Duplicates in Excel

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)

SUM, LEN, SUBSTITUTE Function to Count the Number of a Text String in Excel

Step 2:

  • Press the Enter button.

SUM, LEN, SUBSTITUTE Function to Count the Number of a Text String in Excel

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 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: 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)

Count the Order of Occurrence of Repeated Words in Excel

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

Count Duplicates without First Occurrence in Excel

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"," ")

Count All Repeated Words in a Column without 1st Occurrences

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")

Count All Repeated Words in a Column without 1st Occurrences

Step 4:

  • Hit the Enter button.

Count All Repeated Words in a Column without 1st Occurrences

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

Count the Total of Duplicates in a Column in Excel

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

Combination of Excel SUMPRODUCT and COUNTIF Functions to Get the Total Number of Repetitive Words

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.

VBA to Count the Total Number Repeated Words

Step 2:

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

VBA to Count the Total Number Repeated Words

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

VBA to Count the Total Number Repeated Words

Step 4:

  • Finally, press F5 to run the code.

VBA to Count the Total Number Repeated Words

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo