[ad_1]
Counting the number of distinct values in a spreadsheet is valuable 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 has several different ways to count 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 everything for total flexibility.
Use the COUNTUNIQUE function in Google Sheets
COUNTUNIQUE is one of those Google Sheets features that you’ll appreciate once you start using it. Save time and manual work by counting cells that don’t look like the rest.
RELATED: 9 Basic Google Sheets Features You Should Know
the syntax is COUNTUNIQUE(value1, value2, ...) where only the first argument is required. Let’s look at some examples so you can use the function effectively for different types of data.
To count the number of unique values in cell range A1 to A16, you would use the following formula:
=COUNTUNIQUE(A1:A16)
Maybe you have your own values that you want to insert instead of the ones displayed inside the cells. With this formula, you can count the number of unique values you insert:
=COUNTUNIQUE(1,2,3,2,3,4)
Here, the result is 4 because the values 1, 2, 3, and 4 are unique no matter how many times they appear.
For the example below, you can count the inserted values as shown above combined with values in a range of cells. You would use this formula:
=COUNTUNIQUE(1,2,3,A2:A3)
In this case, the result is 5. The numbers 1, 2, and 3 are unique, as are values within cell range A2 through A3.
If you want to include words as embedded values, the function also counts them as unique elements. Look at this formula:
=COUNTUNIQUE(1,2,3,"word",4)
The result is 5 because each value in the formula is different, whether it is a number or a text.
For the ultimate merge, you can use a formula like this to count the inserted values, text, and a range of cells:
=COUNTUNIQUE(1,2,3,"word",A2:A3)
The result here is 6 which counts the numbers 1, 2 and 3, the text and the unique values in the range A2 to A3.
Use the COUNTUNIQUEIFS function to add criteria
Not everything in a spreadsheet is simple. If you like the idea of the COUNTUNIQUE function but would like to count unique values based on criteria, you can use COUNTUNIQUEIFS. The nice thing about this function is that you can use one or more sets of ranges and conditions.
RELATED: How to Count Data Match Set Criteria in Google Sheets
the syntax is COUNTUNIQUEIFS(count_range, criteria_range1, criteria, criteria_range2, criteria2, ...) where the first three arguments are required.
In this first example, we want to count the unique values in the range A2 to A6 where the value in the range F2 to F6 is greater than 20. This is the formula:
=COUNTUNIQUEIFS(A2:A6,F2:F6,">20")
The result here is 2. Although there are three values greater than 20 in the range F2 to F6, the function returns only those that are unique to the range A2 to A6, these being Wilma Flintstone and Bruce Banner. The latter appears twice.
Let’s use this function with text criteria. To count the unique values in the range A2 to A6 where the text in the range E2 to E6 equals Delivered, you would use this formula:
=COUNTUNIQUEIFS(A2:A6,E2:E6,"Delivered")
In this case, the result is also 2. Although we have three marked as Delivered, only two names in our range from A2 to A6 are unique, Marge Simpson and Bruce Banner. Again, Bruce Banner appears twice.
Just like counting the number of unique values in your spreadsheet, it’s also helpful to highlight duplicates in Google Sheets or remove duplicates altogether.
[ad_2]
