When You’re using Google Sheets, you may have a large amount of data.
You may have repeated values in it that you may want to count how many repeated values are in it, how many duplicates are in it or to display the occurrences of each unique value.
Counting unique values manually is not an efficient way especially when dealing with a large amount of data.
In this tutorial, I’ll cover the 4 methods to automatically count unique values in Google Sheets.
Method 1: Using COUNTUNIQUE to count the unique values in google sheets
This is a very easy and simple way that just count the number of unique values.
1. Choose the cells you wish to count, (A1:A15) in our example.
2. Choose a cell that is not our cell (C2 for instance) and enter the formula in it =COUNTUNIQUE(A1:A15)
3. Press Enter and you’ll see the number of unique values displayed in the C2 cell.
Check out How To Make A Venn Diagram On Google Docs.
Method 2: Using UNIQUE to display the unique values in google sheets
In this method, you can display the unique values not the numbers, with the same steps of the previous method, but only removing COUNT from the previous formula.
1. Choose the cells you wish to count, (A1:A15) in our example.
2. Choose a cell that is not our cell (C2 for instance) and enter the formula in it =UNIQUE(A1:A15)
3. Press Enter and you’ll see the number of unique values displayed in the C2 cell.
Check out How To Find Slope On Google Sheets.
Method 3: Using UNIQUE and COUNTIF to display the occurrences of each unique value in google sheets
In this method, you can count the occurrences of each unique value.
1. Choose the cells you wish to count, (A1:A15) in our example.
Note: You can see the highlighted cells in the Name Box in the upper left of the page (A1:A15).
2. Choose a cell that is not our cell (C2 for instance) and enter the formula in it ={UNIQUE(A1:A10), ARRAYFORMULA(COUNTIF(A1:A10,UNIQUE(A1:A10)))}
Note: This is a combination of two formulas:
- UNIQUE to display the unique values.
- ARRAYFORMULA to display the occurrences of each unique value.
3. Press Enter after typing the formula correctly, you can now see a list of the unique values and the occurrences of each value.
Method 4: Using Remove duplicates to display the unique values in google sheets
You can use this method if you want to remove duplicates and only display the unrepeated unique values.
This method will give you a column of unrepeated values only.
1. Choose the cells you wish to count, (A1:A15) in our example.
2. Tap on Data on the toolbar
3. Click on Data cleanup, then Remove duplicates.
4. When you click Remove duplicates, a dialogue box will appear, asking you to confirm the range of cells. You can click the green Remove duplicates button if the range of cells is correct.
5. You can now see another dialogue box, stating the number of rows removed and the unique rows that are remaining.
6. Click on the OK button to close the dialogue box and you’ll see that the duplicates are now removed.
In a nutshell
By using the methods we provided in this tutorial, you can now count the unique values, display the unique values, remove the duplicates and display the occurrences of each unique value easily, automatically and error-free.
