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.


Method 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, one as a criteria and the other as the result. Now, we will count how many times Banana is repeated in the dataset.

Steps:

  • Go to Cell C17 and input the formula below:
=COUNTIF(C5:C14,C16)

Count the Number of Repeated Words Using the COUNTIF Function

  • Press Enter.

Count the Number of Repeated Words Using the COUNTIF Function

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

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


Method 2 – COUNTIFS Function to Count Duplicates in Excel

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

Steps:

  • Go to Cell C17.
  • Copy the following formula into it:
=COUNTIFS(C5:C14,C16)

COUNTIFS Function to Count Duplicates in Excel

  • Press the Enter key.

Like the COUNTIF function, this function is also case-insensitive so it detects the lowercase data in cell C11.

Related Content: How to Use COUNTIF Formula to Find Duplicates


Method 3 – Excel SUMPRODUCT Function to Count Repeated Terms

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

Steps:

  • Go to cell C17 and copy the formula below:
=SUMPRODUCT(--(C16=C5:C14))

Excel SUMPRODUCT Function to Count Repeated Terms

  • Hit Enter.

SUMPRODUCT is similarly case-insensitive.

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


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

Steps:

  • Select cell C17.
  • Copy the formula below and paste it into that cell:
=SUM(--EXACT(C5:C14,C16))

Count Case Sensitive Duplicates in Excel

  • Hit Enter.

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

  • 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


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

Steps:

  • Enter the following formula in 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

  • Press Enter.

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


Method 6 – Count the Order of Occurrence of Repeated Words in Excel

We added a column named Order for this dataset.

Steps:

  • Go to cell D5.
  • Copy the following formula:
=COUNTIF($C$5:$C5,C5)

Count the Order of Occurrence of Repeated Words in Excel

  • Hit the Enter key and drag the Fill Handle to fill the column.

Read More: Excel VBA to Count Duplicates in a Column


Method 7 – Count Duplicates without First Occurrence

Steps:

  • Go to Cell C17 and put the following formula:
=COUNTIF(C5:C14,C16)-1

Count Duplicates without First Occurrence in Excel

  • Hit the Enter key.


Method 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 compares between values.

We added one new column and a row in the dataset for the demonstration.

Steps:

  • Go to Cell D5.
  • Copy the following formula:
=IF(COUNTIF($C$5:$C5,C5)>1,"Repeated"," ")

Count All Repeated Words in a Column without 1st Occurrences

  • Hit Enter and pull the Fill Handle icon.

This formula checks if there are any repeated values in the range C5 to C14. If it finds a repeated value, it puts a text string as a result.

  • Put a formula in Cell D16 to count the repetitions:
=COUNTIF(D5:D14,"Repeated")

Count All Repeated Words in a Column without 1st Occurrences

  • Hit the Enter key.

Count All Repeated Words in a Column without 1st Occurrences


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

Steps:

  • Copy the following formula into 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

  • Hit Enter.

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


Method 10 – Combination of SUMPRODUCT and COUNTIF Functions to Get the Total Number of Repetitive Words

Steps:

  • 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

  • Hit Enter button.


Method 11 – VBA to Count the Total Number Repeated Words

Step 1:

  • Go to the Developer tab.
  • Click on Record Macro, set a name for the macro, and press OK.

VBA to Count the Total Number Repeated Words

  • {ress the Macros option.
  • Select the desired macro and Step Into it.

VBA to Count the Total Number Repeated Words

  • Copy 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

  • Press F5 to run the code.

VBA to Count the Total Number Repeated Words


Download Practice Workbook

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


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

Get FREE Advanced Excel Exercises with Solutions!
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