How to Count Unique Numeric Values in Excel
In case your Excel list consists of both types of values numeric as well as text. But you want the Count of Numeric that is unique in the list. In that case, you can use the ISNUMBER function in the formula.
Syntax :
” =SUM(IF(ISNUMBER(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0)) “
Count Case – Sensitive Unique Values in Excel
The simple way to count unique values when you have Case-Sensitive data would be creating a helper column with the following array formula to identify duplicates and unique items.
Formula:
” =IF(SUM((–Exact($A$2:$A$10,A2)))=1,”Unique”,”Duplictae”) “
and then use a COUNTIF function to count unique values:
” =COUNTIF(B2:B10,”Unique”) “
In the above image, you can see the Count of unique values is 5.
How to Count Unique Values in Excel? 5 Easy Ways
Count Unique values in Excel is a feature that can help you to find the values that occur only once in the huge list of Excel. We often need to report the Unique number of customers purchased, the number of products in our stock, List of regions our business covered.
In this article, we explain how to count unique values in an Excel column.
Before moving ahead you should know the difference between unique values and distinct values.
- Unique Values: These are the values that occur only once. Unique Values from the list {1,2,3,1,4,2,7,5,7} are {3,4,5}.
- Distinct Values: These are the Values that are different. Distinct values from the list are {1,2,3,1,4,2,7,5,7} are {1,2,3,4,5,7}.