How To Count Unique Values in Google Sheets
If you have been using Google Sheets for some time now, you might already be familiar with the different functions for counting cells. Depending on what type of cells you want to count—blank or non-blank, colored or bold—there are various functions that can help you. But what happens when your spreadsheet contains unique values? This is where the COUNTUNIQUE formula comes in.
In this article, we’ll discuss how to count unique values in Google Sheets using the COUNTUNIQUE formula. We’ll also talk about counting unique values in a single column.
How to Count Unique Values in Google Sheets
Knowing how to count cells in Google Sheets can be useful for many reasons, especially if you’re working with a large dataset. If you only have a small amount of data, you would be able to count the number of specific cells manually. Google Sheets offers various functions for counting different types of cells in a specific data range, including blank cells, text cells, colored cells, bold cells, and more. These are called COUNT functions.
In some cases, you might be required to know the number of unique values in your spreadsheet. For example, you might need to know how many times a certain name or product appears in your spreadsheet. For this, we will be using the COUNTUNIQUE function. It’s used to count the number of unique values in a range or some kind of specified list. To be more precise, you can use the COUNTUNIQUE formula for dates, numbers, names, products, cell references, or random data.
You can also use the COUNTUNIQUE formula to see if there are any duplicates of the unique value. This helps you find errors in your spreadsheet and makes it generally easier to keep track of all your data.
To count unique values in Google Sheets, follow the steps below.
- Open Google Sheets on your preferred browser.
- Go to your spreadsheet or create a new one.
- Double-click on a random empty cell.
- Type in “=COUNTUNIQUE” and an open parenthesis. Make sure to delete any space between the formula and the opening parenthesis.
- Enter the range after the parenthesis. For example, “(A2:C30).”
Note: The first letter represents the start column and the second letter indicates the end column. The numbers signify where the rows start and finish.
- Close the parentheses. The entire formula should look like this: “=COUNTUNIQUE(A2:C30)” without spaces.
- Press “Enter” on your keyboard.
The number of the unique values in your spreadsheet will appear in the empty cell where you wrote the formula. For example, if you see the number “7” in the cell, it means there are seven instances of unique values in the spreadsheet.
The COUNTUNIQUE formula is used to count the number of unique values within a specific range. But what if you want to find the exact unique values? In this case, we will be using the UNIQUE function, without the COUNT.
For example, say you wrote the names of all your students in one spreadsheet, and you want to see how many students have the same name. Similarly, if you have compiled a list of various products, the UNIQUE function can help you identify which product names are duplicates.
Here’s how you can use the UNIQUE function to display the unique values in your Google Sheets spreadsheet.
- Go to Google Sheets and open your spreadsheet.
- Double-click on an empty cell anywhere in the spreadsheet.
- Type in the formula “=UNIQUE” in the empty cell.
- Enter the range in parentheses. For example, “=UNIQUE(A2:B39).”
- Press “Enter” on the keyboard.
All the unique values will be displayed in the form of a list. You can use this formula to see if there are any duplicates in the sheet and then remove them if that’s your goal.
Google Sheets Count Unique Values in Column
Using the COUNTUNIQUE formula to count unique values in a single column is even easier than doing so for a bigger range. Here’s how it’s done.
- Launch Google Sheets.
- Go to your spreadsheet or start one from scratch.
- Select a random empty cell and double-click on it.
- Enter “=COUNTUNIQUE” in the blank cell.
- Write the range in parentheses. Since we’re counting unique values within the same column, it should look like this: “(A2:A40).”
- Here’s what the whole formula should look like: “=COUNTUNIQUE(A2:A40).”
- Press the “Enter” key.
That’s all there is to it. The number of unique values will appear in the cell where you entered the formula.
Keep Track of All the Unique Values in Google Sheets
Although using functions to count values in Google Sheets may seem confusing at first, you’ll get the hang of it in no time. The COUNTUNIQUE formula is very useful for counting unique values in a selected data range, and it can even help you find duplicates.
Have you ever used the COUNTUNIQUE function to count unique values in Google Sheets? Did you use the instructions from this guide, or another method? Tell us about your experience with Google Sheets functions in the comments section below.