Counting the number of distinct values in a spreadsheet is useful in many situations. Whether it’s customer names, product numbers, or dates, a simple function can help you count unique values in Google Sheets.
Unlike Microsoft Excel, which offers multiple ways to count distinct values depending on your version of Excel, Google Sheets offers a handy feature that uses a basic formula. Fortunately, the function works with numbers, text, cell references, inserted values, and combinations of all of these for complete flexibility.
HOW TO ADD TEXT WITH A FORMULA IN GOOGLE SHEETS
Use the COUNTUNIQUE function in Google Sheets
COUNTUNIQUE is one of those Google Sheets features that you’ll appreciate as soon as you start using it. Save time and manual labor by counting cells that don’t look like others.
The syntax is COUNTUNIQUE(value1, value2, …) where only the first argument is required. Let’s look at some examples so that you can use the function effectively for different types of data.
To count the number of unique values in the range of cells A1 to A16, you need to use the following formula:
COUNTUNIQUE for a beach
You may have your own values to insert rather than those displayed in the cells. With this formula, you can count the number of unique values you insert:
Here the result is 4 because the values 1, 2, 3 and 4 are unique no matter how many times they appear.
COUNTUNIQUE for inserted values
For the following example, you can count the values inserted as shown above, combined with the values of a range of cells. You will use the following formula
In this case, the result is 5. The digits 1, 2, and 3 are unique, as are the values in the range of cells A2 through A3.
COUNTUNIQUE for a range and inserted values
If you want to include words as inserted values, the function also counts them as unique elements. Look at this formula:
The result is 5 because each value in the formula is distinct, whether it is a number or a text.
COUNTUNIQUE for inserted values and text
For the ultimate combination, you can use a formula like this to count inserted values, text, and a range of cells:
The result here is 6, which counts the numbers 1, 2, and 3, text, and unique values in the range A2 through A3.
COUNTUNIQUE for a range, inserted values and text
Use the COUNTUNIQUEIFS function to add criteria
Not everything is simple in a spreadsheet. If you like the idea of the COUNTUNIQUE function but want to count unique values based on criteria, you can use COUNTUNIQUEIFS. The advantage of this function is that you can use one or more sets of ranges and conditions.
The syntax is COUNTUNIQUEIFS(count_range, criteria_range1, criteria, criteria_range2, criteria2, …) where the first three arguments are mandatory.
In this first example, we want to count unique values in the range A2 through A6 where the value in the range F2 through F6 is greater than 20. Here is the formula:
The result is 2. Although there are three values greater than 20 in the range F2 to F6, the function only provides unique ones in the range A2 to A6, namely Wilma Flintstone and Bruce Banner. This appears twice.
COUNTUNIQUEIFS for values greater than one
Let’s use this function with textual criteria. To count unique values in the range A2 through A6 where text in the range E2 through E6 equals Delivered, you would use this formula:
In this case, the result is also 2. Although we have three names marked as delivered, only two names in our A2 to A6 range are unique, Marge Simpson and Bruce Banner. Again, Bruce Banner appears twice.
COUNTUNIQUEIFS for text
As with counting the number of unique values in your spreadsheet, it’s also useful to highlight duplicates in Google Sheets or remove them altogether.