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:

• Press Enter.

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:

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

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

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

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:

• Press Enter.

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:

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:

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

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

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

• Hit the Enter key.

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.

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

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

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

• Copy the following code on the command module:

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

• Press F5 to run the code.

Download Practice Workbook

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

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