Often you may want to count duplicate values in Google Sheets.
Fortunately this is easy to do using the COUNTIF() function.
The following example shows how to count duplicate values for the following dataset in Google Sheets:
Let’s jump in!
Example: Count Duplicates in Google Sheets
Suppose we have the following dataset that contains the names of various basketball teams:
We can type the following formula into cell B2 to generate a list of unique team names from column A:
=UNIQUE(A2:A14)
The following screenshot shows how to use this formula in practice:
We can then type the following formula into cell C2 to count the number of duplicate values for the Mavs team:
=COUNTIF($A$2:$A$14, B2) - 1
We can then click and drag this formula down to each remaining cell in column C to count the number of duplicate values for each team:
From the output we can see:
- Mavs have 3 duplicate values.
- Warriors have 2 duplicate values.
- Hawks have 1 duplicate value.
- Kings have 0 duplicate values.
And so on.
Note that his formula counts how many times each unique value occurs and then subtracts one.
Thus, if a team name only occurs once, then the formula returns 0 to indicate that the team has 0 duplicate values.
If you’d like to calculate the total number of duplicate values in the entire dataset, you can type the following formula into cell D2:
=SUM(C2:C8)
From the output we can see that there are 6 total duplicate team names in the dataset.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
How to Use COUNTIF Contains in Google Sheets
How to Use COUNTIF with Wildcards in Google Sheets
How to Count Cells with Text in Google Sheets