HomeTechnologyNewsHow to count unique values ​​in google sheets

How to count unique values ​​in google sheets


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)

COUNTUNIQUE for a range

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.

COUNTUNIQUE for inserted values

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.

COUNTUNIQUE for a range and inserted values

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.

COUNTUNIQUE for inserted values ​​and 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.

COUNTUNIQUE for a range, inserted values, and text

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.

COUNTUNIQUEIFS for greater than a value

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.

COUNTUNIQUEIFS for text

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.

Must Read

%d bloggers like this: