HOW TO FIND AND COUNT UNIQUE VALUES, UNIQUE TEXT VALUES OR UNIQUE NUMBERS IN GOOGLE SHEETS?

Table of Contents

INTRODUCTION

Whenever we prepare any report in GOOGLE SHEETS, we have two constituents in any report.

The Text portion and the Numerical portion.

But just storing the text and numbers doesn’t make the super reports. Many times we need to automate the process in the reports to minimize the effort and improve the accuracy.

Many functions are provided by the GOOGLE SHEETS which work on Text and give us the useful output as well. But few problems are still left on which we need to apply some tricks with the available tools.

IN THIS ARTICLE, WE’LL LEARN TO COUNT THE UNIQUE TEXT VALUES IN GOOGLE SHEETS.

HOW TEXT IS HANDLED IN GOOGLE SHEETS?

TEXT is simply the group of characters and strings of characters that convey the information about the different data and numbers in GOOGLE SHEETS. Every character is connected with a code [ANSI].

Text comprises of the individual entity character which is the smallest bit that would be found in GOOGLE SHEETS. We can perform the operations on the strings[Text] or the characters.

Characters are not limited to A to Z or a to z but many symbols are also included in this which we would see in the latter part of the article. 

TEXT IS AN INACTIVE NUMBER TYPE[FORMAT] IN GOOGLE SHEETS. ANYTHING STORED AS TEXT [NUMBER OR DATE] WON’T RESPOND TO ANY STANDARD FORMULAS OR FUNCTIONS BUT SPECIALLY DESIGNED TEXT FUNCTIONS. [EXCEPTIONS DO OCCUR IN THE CASE OF NUMBERS]

If we need to make anything inactive, such as a Date to be non responding to the calculation, we put it as a text. Similarly, if we want to avoid any calculations for a number it needs to be put as a text.  

WHAT ARE UNIQUE VALUES?

Literally, Unique values are the ones that exist only once in the complete data but in general, and the default output of Unique function, unique values can also be taken as all the different values present in the data.

The correct word for finding all the available different types of values is DISTINCT VALUES.

In this article, we’ll take unique values as all the different types of values including one separate example showing the numbers which appears exactly once in the given data.

For example, suppose we have a data as

1,2,3,3,4,5,6,6,5,4,7,8,9,0,9,0

if we want to find out the unique values as per GOOGLE SHEETS, it’ll give us the output as 1,2,3,4,5,6,7,8,9,0 i.e. 10 will be the count of the result of unique values as per GOOGLE SHEETS but if we use the standard English language definition or exact once condition, only 1,2,7,8 are the four values which exist only once and the count will be 4.

If you want to find out the values which exist only once or twice and so on…. you can visit here.

But if you want to know all the distinct numbers which exist in the range, you can continue reading this article.

FIND AND COUNT UNIQUE TEXT VALUES IN GOOGLE SHEETS-SIMPLE SCENARIO

This can be a requirement sometimes when we need to count the unique text values in a given data or column. It can be done in a number of ways. Let us discuss a few.

SIMPLE SCENARIO: YOUR COLUMNS CONTAIN THE TEXT ONLY WITHOUT ANY BLANKS OR NUMBERS 
MIXED SCENARIO IS DISCUSSED IN THE NEXT SECTION

1. USING REMOVE DUPLICATES

2. USING UNIQUE FUNCTION

FIND AND COUNT UNIQUE VALUES USING REMOVE DUPLICATES:

This is an option already available in GOOGLE SHEETS directly and is very easy to use.

THIS OPTION IS NOT AUTOMATIC AND SHOULD BE USED ONLY IF WE DON’T HAVE MUCH DATA TO BE ANALYZED.

The function is not automatic means that we need to manually use this utility every time.

STEPS TO USE REMOVE DUPLICATES TO COUNT THE UNIQUE TEXT VALUES IN GOOGLE SHEETS

[*KINDLY VISIT HERE FOR COMPLETE INFORMATION ABOUT THE PROCEDURE OF REMOVING DUPLICATES IN GOOGLE SHEETS]

  • Select the complete data or column where we want to find the number of unique values. [ If we need the original column intact, copy the column and paste it somewhere else. We can perform operations on this column].
SAMPLE DATA
  • Go to DATA MENU>DATA CLEANUP> REMOVE DUPLICATES.
  • Select the column from the dialog box. We have only one column so choose the column.
SELECT THE COLUMN
  • After selecting the column, press OK.
  • It’ll leave only unique values in the column. In this way, we can find the unique values.
  • Now , we can put the formula =COUNTA(FIRST CELL OF COLUMN: LAST CELL OF COLUMN). [HELP: COUNTA FUNCTION]
  • It’ll give us the number of unique text values present in the column.
STEPS TO COUNT THE TOTAL NUMBER OF UNIQUE TEXT IN GOOGLE SHEETS

FIND AND COUNT UNIQUE VALUES USING UNIQUE FUNCTION

In this method, we will make use of UNIQUE FUNCTION which will help us to find out the unique values or texts in the given column. After getting all the unique values we can count them easily. Let us start.

We’ll take an example for trying our procedure.

Suppose we have the following data.

SAMPLE DATA

STEPS TO COUNT UNIQUE TEXT VALUES IN A GOOGLE SHEETS USING UNIQUE FUNCTION.

[THE COMPLETE PICTORIAL REPRESENTATION OF THE USE OF UNIQUE FUNCTION IS HERE].

We have to process the information twice to get the desired results. First of all we need to find the unique values.

  • Select the cell where we want to find the unique values.
  • Put the following formula.
  • =UNIQUE(ARRAY,COLUMN-WISE, FALSE)
  • For our example, the formula will be
  • =UNIQUE(G6:G17, FALSE,0) [The first argument is the array containing the data. The second argument tell it that it has to do the comparison in the rows and the third tells that it can count the entries which are more than one].
  • The resulting array will contain only the unique values.
  • In the next column, we can count these by the use of COUNTA FUNCTION as =COUNTA(H6:H17). We can see in the picture that we had only 4 values but we took a wide range. The reason is that we don’t know what is the number of unique values if the data is too large.
  • Take a look at the picture below for reference.
STEPS TO COUNT THE TOTAL NUMBER OF UNIQUE TEXT IN GOOGLE SHEETS


FIND AND COUNT THE UNIQUE VALUES IN GOOGLE SHEETS WHICH APPEAR EXACTLY ONCE

So, if we want to find out the unique values which will enlist only the values which appear only once, we can make use of the following solution.

FOLLOW THE STEPS TO COUNT AND FIND OUT THE UNIQUE VALUES IN GOOGLE SHEETS WHICH APPEARS EXACTLY ONCE

In the adjacent column or any column where you want the unique values, enter the formula as

=UNIQUE(E4:E15,FALSE,TRUE)

This function will result in all the values which exist only once in the given data.

FINDING THE UNIQUE VALUES EXISTING ONCE ONLY

COUNT THE UNIQUE VALUES WHICH EXIST ONLY ONCE IN THE GIVEN DATA IN GOOGLE SHEETS

  1. Select the cell whre you want the result.
  2. Enter the formula as =COUNTA(F4:F15).
  3. The result will appear as shown in the picture below.
  4. KIND is the only word which appears only once in the given data.

COUNT THE NUMBER OF VALUES APPEARING EXACTLY ONCE

MIXED SCENARIO

So, the cases we just discussed deal with the data where we have text only with or without any blanks. [ Because if there are any blanks, our intermediate step will remove those and keep only the single values. ] But what if we have a mix i.e. some numbers are also there in the same column. 

MIXED SCENARIO: YOUR COLUMNS OR RANGES CONTAIN THE TEXT, NUMBERS AND BLANKS. 
Different cases are discussed below.



COUNT THE UNIQUE VALUES IN A COLUMN OR RANGE CONTAINING NUMBERS, TEXT OR BLANK CELLS IN GOOGLE SHEETS SHEET

Let us discuss the cases when there is a mix of numbers, text or blanks etc. too.

EXAMPLE:

Let us take an example of mixed data.

11
2
54
3
5
HELLO
2
WHAT
ARE
YOU
YOU
WHAT
3
5

Let us try to find out the way to count the unique values present in this data.

FOLLOW THE STEPS TO FIND THE UNIQUE VALUES IN THE GIVEN DATA.

  1. Enter the formula at the top cell where you want the result as =SUM(IFERROR(1/COUNTIF(H27:H41,H27:H41),0)) where H27:H41 is the range on which we apply this check. We can go for a complete column also.
  2. Press Enter.
  3. The result will appear.

COUNT UNIQUE VALUES IN GOOGLE SHEETS

The above procedure will find out the total number of different types i.e. unique values whether it is a number or text.



COUNT THE UNIQUE TEXT VALUES IN GOOGLE SHEETS IGNORING NUMBERS AND BLANKS

So, the cases we just discussed deal with the data where we have text only with or without any blanks. [ Because if there are any blanks, our intermediate step will remove those and keep only the single values. ]

But what if we have a mix i.e. some numbers are also there in the same column. [ Although it happens quite less because a column is dedicated for a particular value only. It can be a text or number only.]

But even then, we’ll discuss the way to tackle the same.

EXAMPLE:

Let us take an example of mixed data.

11
2
54
3
5
HELLO
2
WHAT
ARE
YOU
YOU
WHAT
3
5

Let us try to find out the way to count the unique values present in this data.

FOLLOW THE STEPS TO FIND THE UNIQUE VALUES IN THE GIVEN DATA.

  1. Enter the formula at the top cell where you want the result as =SUM(ISTEXT(H4:H18)*IFERROR(1/COUNTIF(H4:H18,H4:H18),0)) where H4:H18 is the range on which we apply this check. We can go for a complete column also.
  2. Press Enter.
  3. The result will appear.

COUNT UNIQUE TEXT VALUES IN GOOGLE SHEETS

The above procedure will find out the total number of different types of TEXT VALUES available ignoring all the numbers or blanks.



COUNT THE UNIQUE NUMBER VALUES IN GOOGLE SHEETS IGNORING TEXT VALUES AND BLANKS

Just like the previous case, where we discussed counting the unique text values only ignoring the blanks and numbers , let us now try to count the unique number values only ignoring the text and blanks.

But even then, we’ll discuss the way to tackle the same.

EXAMPLE:

Let us take an example of mixed data.

11
2
54
3
5
HELLO
2
WHAT
ARE
YOU
YOU
WHAT
3
5

Let us try to find out the way to count the unique values present in this data.

FOLLOW THE STEPS TO FIND THE UNIQUE VALUES IN THE GIVEN DATA.

  1. Enter the formula at the top cell where you want the result as =SUM(ISNUMBER(H27:H41)*IFERROR(1/COUNTIF(H27:H41,H27:H41),0)) where H27:H41 is the range on which we apply this check. We can go for a complete column also.
  2. Press Enter.
  3. The result will appear.

COUNT UNIQUE NUMBER VALUES IN GOOGLE SHEETS

The above procedure will find out the total number of different types i.e. unique NUMBER TYPE VALUES available ignoring all the text or blanks.