Count no. of unique texts in a column

0

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.

Answer
Discuss

Answers

0

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.

Discuss
0

DearAll,

we can use also the following formula.

=COUNTA(UNIQUE(C5:C13))

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login