HOW TO COUNT THE DISTINCT VALUES IN EXCEL?

Table of Contents

INTRODUCTION

Whenever we prepare any report in excel, 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 Excel 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.

We already learnt many tricks to handle text in the article MANIPULATING TEXT IN EXCEL – PART I

IN THIS ARTICLE, WE’LL LEARN TO COUNT THE DISTINCT VALUES IN EXCEL.

Precisely, we’ll learn the way to find out the values which are unique (no repetition) or repeated twice or thrice or any specific number of times in the given data.

HOW TEXT IS HANDLED IN EXCEL?

TEXT is simply the group of characters and strings of characters that convey the information about the different data and numbers in Excel. 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 Excel. 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 later part of the article. 

TEXT IS AN INACTIVE NUMBER TYPE[FORMAT] IN EXCEL. 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 nonresponding 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 DISTINCT VALUES?

Distinct values simply mean, all the different values available.

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 distinct values, 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 distinct values as per excel.

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 exists in the range ,you can continue reading this article.

FIND DISTINCT VALUES IN EXCEL

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 DISTINCT VALUES USING REMOVE DUPLICATES:

This is an option already available in Excel 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.

STEPS TO USE REMOVE DUPLICATES TO COUNT THE DISTINCT TEXT VALUES IN EXCEL

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

  • Select the complete data or column where we want to find the number of distinct 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 HOME>DATA>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 distinct values in the column.
  • 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 distinct text values present in the column.
STEPS TO COUNT THE TOTAL NUMBER OF UNIQUE TEXT IN EXCEL

FIND AND COUNT DISTINCT VALUES USING UNIQUE FUNCTION

In this method, we will make use of UNIQUE FUNCTION which will help us to find out the unique or distinct values or texts in the given column. After getting all the distinct 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 EXCEL 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 distinct values.

  • Select the cell where we want to find the result.
  • 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 distinct 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 distinct 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 EXCEL


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 DISTINCT NUMBERS IN EXCEL WHICH EXIST ONLY ONCE

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 distinct values in Excel which occur exactly once.

FOLLOW THE STEPS TO FIND THE `DISTINCT VALUES WHICH ARE NOT REPEATED

  1. Enter the formula at the top cell where you want the result as =SUM(IF(COUNTIF(H4:H18,H4:H18)=1,1,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 NUMBER OF VALUES APPEARING ONCE

The above procedure will find out the total number of different values but appearing only once in the given data.



COUNT THE DISTINCT NUMBERS IN EXCEL WHICH EXIST EXACTLY TWICE OR MORE TIMES IN THE GIVEN DATA

We just learn the way to find out the distinct or all the different numbers which exist exactly once in the given data or which have the frequency as 1 in the given data.

Let us now learn the way to find out the numbers or values which appear exactly twice in the given data.

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 find the values which occur exactly twice or two times in the given data.

FOLLOW THE STEPS TO FIND THE DISTINCT VALUES IN THE GIVEN DATA APPEARING TWICE.

  1. Enter the formula at the top cell where you want the result as =SUM(IF(COUNTIF(H27:H41,H27:H41)=2,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.
  4. The result will show the count of the values which are repeated only twice.

COUNT VALUES WHICH APPEAR TWICE IN THE GIVEN DATA

We can verify the result by manually checking if there are 5 values that appear exactly twice which are 2,3,5, WHAT, YOU

On the same lines, we can generalize the steps to count the number of values, numbers, or text, which repeat a certain number of times in the given data.



COUNT THE DISTINCT VALUES WHICH REPEAT N NUMBER OF TIMES

On similar lines, we can make the formula useful in searching for the count of any number of repetitions in Excel data.

Use the function as

=SUM(IF(COUNTIF(H27:H41,H27:H41)=number of repetitions,1/COUNTIF(H27:H41,H27:H41),0))

where number of repetitions is N. N is the number that will be equated to the number of repetitions.

EXAMPLE:

Let us take an example of mixed data.

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

Let us try to find out the way to search the distinct values appearing in the given data.

FOLLOW THE STEPS TO FIND THE VALUES WHICH APPEARS THREE TIMES IN THE GIVEN DATA

  1. Enter the formula at the top cell where you want the result as =SUM(IF(COUNTIF(H48:H62,H48:H62)=3,1/COUNTIF(H48:H62,H48:H62),0)) where H48:H62 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 EXCEL

The above procedure shows the result as 1 as HELLO is the word which is being repeated thrice.

So, in this way, we can find out any number of repetitions in the given data.