Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count No of Types.
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 ================================================== === |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count No of Types.
Mike Sir,
Excellent solution. Thank a lot. 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 ================================================== === |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count No of Types.
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 ================================================== === |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count No of Types.
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 -- R. Khoshravan Please click "Yes" if it is helpful. "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 ================================================== === |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ================================================== === |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count No of Types.
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 ================================================== === |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
QPW file types | Excel Discussion (Misc queries) | |||
chart types | Excel Worksheet Functions | |||
Types of Files | Excel Discussion (Misc queries) | |||
Encryption Types | Excel Discussion (Misc queries) |