Count No of Types.
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" wrote:
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
================================================== ========
"Mike H" wrote:
Try
=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
Mike
"HARSHAWARDHAN. S .SHASTRI" wrote:
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
================================================== ===
|