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
