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)

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

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