Count No of Types.

[HARSHAWARDHAN. S .SHASTRI]

Hello,

Column A consisting data , what i need is count no of types in this data.

A

B
B
C
D
D
E
Y
Z


In above example answer should be "6" as B,C,DE,Y and Z are only variety
of types.

H S Shastri

=====================================================

 

[HARSHAWARDHAN. S .SHASTRI]

Mike Sir,

Excellent solution.

Thank a lot.


H S Shastri


========================================================

 

[Mike H]

Try

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

Mike

 

[Khoshravan]

One way is to get the unique list and then count them.
To get unique list do as follows:
Data| Filter| Advanced filter
In action select: copy to another location
Select the List range (or it is selected automatically)
Put a reference cell for: copy to range
tick: Unique records only
Then you get the number by a count function

 

[Mike H]

Hi,

This bit is easy, it returns an array of True or false and remember that
true is the same as 1 and false is the same as 0. So this returns an array of
10 TRUE if every cell in the range is populated

SUMPRODUCT((A1:A10<>"")

The next bit returns an array of numbers. If an item appears once in the
range it returns a 1. If the same tems appears 3 times it returns a 3

COUNTIF(A1:A10,A1:A10&""))

we then divide the 2 arrays and here's a shortened example of where a1 to A4
contained

a,a,b,b


TRUE TRUE TRUE TRUE
2 2 2 2

True/2= .5

sumproduct these and you get your answer of 2 which is four halfs added
togother.

Mike

 

[HARSHAWARDHAN. S .SHASTRI]

Sir i am getting the results but unable to understand the logic behind
formula . Will you pl elaborate the formula.

Thanks in advance.

H S Shastri


==========================================================