Step 1: This is an inbuilt function in Excel, for this, you have to go to the Data tab and in Sort & Filter group click on Advanced. In the image below you can see the result obtained by implementing the formula above: You can see in the picture above that it counting only the unique text value by avoiding the blank cells and cells having the numeric value.ДЇor counting the unique value in the context of number in a range of cell that has text data, numeric data and blank cells we constructed the following formula: =SUM(ISTEXT(A2:A10)/COUNTIF(A2:A10, A2:A10&вЂќвЂќ)) (press Ctrl+Shift+Enter, as it a array formula) In this section, we will see what we can do if have the reference range that has both numeric and text data and we want to count unique values only in the context of text. Now you can see it showing the correct result now. The formula COUNTIF(A2:A18,вЂќвЂќ&вЂќ*вЂќ) returns 1 as it checks for the cell that are To avoid the counting of the blank cell we will modify the formula in a way:

You can observe the result in the picture above, it is counting blank cell as a unique entry and is counting 1 for it. =SUM(1/COUNTIF(A2:A18,A2:A18&вЂќвЂќ)) (press Ctrl+Shift+Enter, as it a array formula) Well, a little change in the formula used above would help if we modify it in a way: Guess what will happen to the above formulas if the selected range has a blank cell/s. =SUM(1/COUNTIF(A2:A18,A2:A18)) (press Ctrl+Shift+Enter, as it a array formula) =SUM(1/COUNTIF(B2:B18,B2:B18)) (press Ctrl+Shift+Enter, as it a array formula)ДЇor text data the formula below shows the result: The picture below shows the result for the formula: When this SUM executes the result it generates is 8 and the entire array values are divided by 1 now, the SUM formula will have: The array returned by FREQUENCY function is: Like at first occurrence of 100, it shows digit 6, it means 100 occurs 6 times in the reference range, at the rest occurrence of 100 it shows 0.ДЄs we have seen in the figure above FREQUENCY function returns an array, calculating the occurrences of each value in the reference range. See in the image below, at first occurrence of the value it shows, its total occurrence in the reference range, at rest occurrences, it shows 0. =FREQUENCY(B2:B18, B2:B18) ( press Ctrl+Shift+Enter, as it an array formula) For this, first select an output-range (equal to the size of the reference range) and at the top cell of output-range type the formula: it avoids the text values and zeros.Д®xample: Let us check the frequency of each number in the range B2:B18. Note: This function only considers non zero numeric value i.e. It returns a vertical array which shows the frequency of occurrence of each number in the range. Here, we will see the construction of two formulas to count unique values.ДЇREQUENCY: This function counts how many times the value has occurred in the selected range. Steps to Count Unique Values in Excel 2016:ДЄs above we have discussed that you can build your own formula to count the unique values in a range of cells.

In this section we will discuss some handy methods to count the unique values in excel:

There are various functions available in Excel using a combination of which you can implement the formula to count the unique values in Excel.

LetвЂ™s say if you want to identify or count unique values in excel, then you can build up your own formula for it. It becomes messy to sort out the unique values in a large range of cells if it contains duplicate values.

Excel For Mac 2016 Count Distinct