How to Count Unique Values in Excel
Let’s say we have a data set as shown below:
For the purpose of this tutorial, I will name the range A2:A10 as NAMES. Going forward we will use this named range in the formulas. In this data set, there is a repetition in the NAMES range. To get the count of unique names from this dataset (A2:A10), we can use a combination of COUNTIF and SUMPRODUCT functions as shown below: =SUMPRODUCT(1/COUNTIF(NAMES,NAMES))
How does this formula work? Let’s break down this formula to get a better understanding:
COUNTIF(NAMES,NAMES) This part of the formula returns an array. In the above example, it would be {2;2;3;1;3;1;2;3;2}. The numbers here indicate how many times a value occurs in the given range of cells. For example, the name is Bob, which occurs twice in the list, hence it would return the number 2 for Bob. Similarly, Steve occurs thrice and hence 3 is returned for Steve. 1/COUNTIF(NAMES,NAMES) This part of the formula would return an array – {0.5;0.5;0.333333333333333;1;0.333333333333333;1;0.5;0.333333333333333;0.5} Since we have divided 1 by the array, it returns this array. For example, the first element of the array returned above was 2. When 1 is divided by 2, it returns .5. SUMPRODUCT(1/COUNTIF(NAMES,NAMES)) SUMPRODUCT simply adds all these numbers. Note that if Bob occurs twice in the list, the above array returns .5 wherever Bob name appeared in the list. Similarly, since Steve appears thrice in the list, the array returns .3333333 whenever Steve name appears. When we add the numbers for each name, it would always return 1. And if we add all the numbers, it would return the total count of unique names in the list.
This formula works fine until you don’t have any blank cells in the range. But if you have any blank cells, it would return a #DIV/0! error. How to Handle BLANK cells? Let’s first understand why it returns an error when there is a blank cell in the range. Suppose we have the data set as shown below (with cell A3 being blank):
Now we if use the same formula we used above, the COUNTIF part of the formula returns an array {2;0;3;1;3;1;2;3;1}. Since there is no text in cell A3, its count is returned as 0.
And since we are dividing 1 by this entire array, it returns a #DIV/0! error. To handle this division error in case of blank cells, use the below formula: =SUMPRODUCT((1/COUNTIF(NAMES,NAMES&””))) One change that we have made to this formula is the criteria part of the COUNTIF function. We have used NAMES&”” instead of NAMES. By doing this, the formula would return the count of blank cells (earlier it returned 0 where there was a blank cell). NOTE: This formula would count blank cells as a unique value and return it in the result.
In the above example, the result should be 5, but it returns 6 as the blank cell is counted as one of the unique values. Here is the formula that takes care of the blank cells and doesn’t count it in the final result: =SUMPRODUCT((NAMES<>””)/COUNTIF(NAMES,NAMES&””))
In this formula, instead of 1 as the numerator, we have used NAMES<>””. This returns an array of TRUEs and FALSEs. It returns FALSE whenever there is a blank cell. Since TRUE equates to 1 and FALSE equates to 0 in calculations, blank cells are not counted as the numerator is 0 (FALSE). Now that we have the basic skeleton of the formula ready, we can go a step further and count different data types.
How to Count Unique Values in Excel that are Text
We will use the same concept discussed above to create the formula that will only count text values that are unique. Here is the formula that will count unique text values in Excel: =SUMPRODUCT((ISTEXT(NAMES)/COUNTIF(NAMES,NAMES&””))) All we have done is used the formula ISTEXT(NAMES) as the numerator. It returns TRUE when the cell contains text, and FALSE if it doesn’t. It will not count blank cells, but will count cells that have an empty string (“”).
How to Count Unique Values in Excel that are Numeric
Here is the formula that will count unique numeric values in Excel =SUMPRODUCT((ISNUMBER(NAMES))/COUNTIF(NAMES,NAMES&””)) Here, we are using ISNUMBER(NAMES) as the numerator. It returns TRUE when the cell contains numeric data type, and FALSE if it doesn’t. It doesn’t count blank cells.
How to Count Cells that Contain Text Strings. How to Count the Number of Words in Excel. Count Cells Based on Background Color in Excel. Using Multiple Criteria in Excel COUNTIF and COUNTIFS Function.
