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

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

`=COUNTIFS(C5:C14,C16)`

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

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

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

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

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

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

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

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

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

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

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

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

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

- 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 | Learn Excel**