Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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