Whenever it comes to counting values in Excel, you always have to use formulas for this. Having a large dataset needs you to get help from multiple features used in Excel. You often tend to find how many unique values or duplicate values are there in the sheet. On the other hand, sometimes you need to count distinct values in Excel.
Before this post, we explained how you can count non-empty cells in Excel. And now, you can find out the basics of how you can count unique values Excel. Let’s understand what a duplicate value is. In the list of products, you will find duplicate values more than once, whereas a distinct value shows all the different values in the list. So, distinct values are basically unique values and the first appearance of duplicate values.
Unique Values: Unique values appear in the list only once.
Distinct Values: Distinct values are different values that appear in the list, such as unique values and the first appearance of duplicate values.
Below you can see the difference clearly.
How to Count Unique Values Excel
We have some effective methods that can work perfectly in counting unique values in Excel.
Count Unique Values with SUM, IF, and COUNTIF Functions
Excel is always ready to provide multiple tools to execute functions smoothly. For this reason, you can use a combo of SUM, IF, and COUNTIF functions to count unique values in excel. For this function, you can use the following formula:
=SUM(IF(COUNTIF(range, range)=1,1,0))
Put the above formula in the wanted cell and here the range shows the beginning as well as the ending cell. Also, keep in mind this array formula count values in a new array. As you know, this is an array formula, that’s why you need to press CTRL + SHIFT + ENTER keys from the keyboard once the formula is added. Another thing you need to remember is that curly braces will by default added at the end once the formula is added that’s why you don’t need to put these braces manually.
You can find out the unique values from the sheet by adding the formula in the required cell. Put the cells that contain the elements with unique values. Here you will see cells B3 to B16 range and the formula would be as:
=SUM(IF(COUNTIF(B3:B16,B3:B16)=1,1,0))
Now, you have to press CTRL + SHIFT + ENTER keys from the keyboard. By using this formula, you can count unique values from the selected cell range. And here the unique count value would be 3. Let’s understand how this formula can help you in finding unique values:
Using the COUNTIF function will help you find the number of unique values from the range B3 to B16. You will see the number of times the value is repeated. The array that stores these values will be as [1,1,2,3,2,2,2,2,2,3,1,2].
With the IF function, you can record the unique values (=1) by replacing them with anything except 1 with 0. And finally, the array would be as [1,1,0,0,0,0,0,0,0,0,0,0,1,0]. In the end, you will see the SUM function adds the unique value while returning the value 3. We have two extra options when you can use the SUM, IF, and COUNTIF functions. You can find both numeric values as well as unique text values in Excel.
How to Count Unique Text Values in Excel
Sometimes text data in the worksheet gets tangled with numbers, that’s why you may use the function that helps in finding the unique text values in Excel. For this, you need to enter the formula:
=SUM(IF(ISTEXT(range)*COUNTIF(range,range)=1,1,0))
Once this formula is added to the required cell, you have to press CTRL + SHIFT + ENTER key. Using the generic formula, you will need to add the ISTEXT argument that can count the unique text values. The ISTEXT function returns 1 when the value is a text. On the other hand, the value returns 0 when the cell contains a non-text value.
How to Count Unique Numeric Values in Excel
This procedure is used vice versa to the above-mentioned method, when you need to count unique numeric values that get tangled with text, the below formula is used:
=SUM(IF(ISNUMBER(range)*COUNTIF(range,range)=1,1,0))
In this case, the ISNUMBER function will return 1 when you have numeric values. Also, remember that Excel counts dates and times as numbers.
How to Count Case-Sensitive Unique Values in Excel
You must have to make a helper column when your table has case-sensitive data and you need to find the unique values. You will have to use the following array formula to clear the duplicate as well as unique values:
=IF(SUM((–EXACT($A$2:$A$10,A2)))=1, “Unique”,”Dupe”)
Now, you have to enter this COUNTIF function to count unique values:
=COUNTIF(B2:B10, “’unique”)
Final Thoughts
So, basically, this post explains everything that could be needed to count values in Excel, whether these values are unique, duplicate, or distinct. You just have to follow the above-mentioned methods carefully.
