How to Count Unique Text Values in Excel
In case your Excel list contains both text and numerical values and you want to count only unique text values then you can count unique text values in Excel by using ISTEXT Function to the formula.
Syntax:
” =SUM(IF(ISTEXT(A2:A10)*COUNTIF(A22:A10,A2:A10)=1,1,0)) “
ISTEXT functions return TRUE if the checked value is text value or else FALSE. Here in the formula (*) asterisk works as the AND operator in the formula, which means the IF function returns 1 only if a value is both text and unique, Otherwise 0. And the SUM function adds up all 1’s and you can get the count of text values as the output.
Note: Always remember to press Ctrl + Shift + Enter for an array formula.
As you can see the formula returns the value 2, because we have only two text +unique values.
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}.
Contact Us