Often you may want to count the number of times a word repeats in a column in Excel.

For example, suppose you have the following list of basketball team names and you’d like to count the number of repeated names:

The following examples show how to perform the following calculations:

**1.** Count number of times that each unique team name repeats

**2.** Count total repeated team names (including 1st occurrence)

**3.** Count total repeated team names (not including 1st occurrence)

Let’s jump in!

**Example 1: Count Number of Times Each Word Repeats**

To count the number of times that each unique team name repeats, we can first type the following formula into cell **C2** to get a list of unique team names:

=UNIQUE(A2:A11)

Next, we can type the following formula into cell **D2** to count the number of times each team name repeats:

=COUNTIF($A$2:$A$11, C2)

Column D now shows the number of times that each unique team name occurs.

**Example 2: Count Total Repeated Words (Including 1st Occurrence)**

To count the total number of repeats (including the 1st occurrence) in the team column, we can type the following formula into cell **C2**:

=ROWS($A$2:$A$11)-SUM(IF(COUNTIF($A$2:$A$11,$A$2:$A$11) =1,1,0))

The following screenshot shows how to use this formula in practice:

The formula tells us that there are a total of **8** repeated words (including the 1st occurrence) in the team column.

We can verify this is correct by manually identifying the 8 repeated words:

**Example 3: Count Total Repeated Words (Not Including 1st Occurrence)**

To count the total number of repeats (not including the 1st occurrence) in the team column, we can first type the following formula into cell **B2** to determine if each row represents a repeat or not:

=IF(COUNTIF($A$2:$A2,A2)>1,"Yes","No")

We can then click and drag this formula down to each remaining cell in column B:

Next, we can type the following formula into cell **C2** to count the number of total repeated words (not including the 1st occurrence) in the team column:

=COUNTIF(B2:B11, "Yes")

The following screenshot shows how to use this formula in practice:

We can see that there are **5** total repeated words (not including the 1st occurrence) in the team column.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Count If Cells Contain Text

Excel: How to Count Unique Values by Group

Excel: How to Count Unique Values Based on Multiple Criteria