How to count the no. of unique texts In a column?
e.g. in a colum,
Peter
John
Mary
Peter
Mary
Peter
John
John
Mary
The return answer = 3.
How to count the no. of unique texts In a column?
e.g. in a colum,
Peter
John
Mary
Peter
Mary
Peter
John
John
Mary
The return answer = 3.
If there are no blanks in the column you can use the formula below.
=SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15))
If your range includes blanks the above formula will throw an error and that is a good thing if there aren't supposed to be any. However, using the version of the formula given below you can avoid the error.
=SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15&""))
However, this formula will include the blank in the count. In your list the result would be 4 instead of 3. If you want the exclude blanks the formula below will be the one you prefer.
=SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
Note that the count range A1:A15 in all the above examples should be a named range, probably one that designed to be dynamic.
DearAll,
we can use also the following formula.
=COUNTA(UNIQUE(C5:C13))