| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with counting the number of destinct items in a column
This is my situation, I have vendor names in a column, say A for this
example, and there are 5 vendor name in the list, from row1 to row5. In row 6, I want to enter the number of destinct vendor name in row6. See below for example. I don't know if there is a function for this in excel. I would also like to do this in code especially, because it will make things much easier. Any ideas or help will be grately appriciated. Thanks. A 1 TTMI 2 VERIZON 3 ZAYO 4 TTMI 5 ZAYO --------------- 6 3 VENDORS |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with counting the number of destinct items in a column
'Formula
=SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) 'The same in VBA MsgBox Evaluate("=SUMPRODUCT((A1:A5<"""")/COUNTIF(A1:A5,A1:A5&""""))") If this post helps click Yes --------------- Jacob Skaria "Ayo" wrote: This is my situation, I have vendor names in a column, say A for this example, and there are 5 vendor name in the list, from row1 to row5. In row 6, I want to enter the number of destinct vendor name in row6. See below for example. I don't know if there is a function for this in excel. I would also like to do this in code especially, because it will make things much easier. Any ideas or help will be grately appriciated. Thanks. A 1 TTMI 2 VERIZON 3 ZAYO 4 TTMI 5 ZAYO --------------- 6 3 VENDORS |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with counting the number of destinct items in a column
Thanks Jacob. Works great.
"Jacob Skaria" wrote: 'Formula =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) 'The same in VBA MsgBox Evaluate("=SUMPRODUCT((A1:A5<"""")/COUNTIF(A1:A5,A1:A5&""""))") If this post helps click Yes --------------- Jacob Skaria "Ayo" wrote: This is my situation, I have vendor names in a column, say A for this example, and there are 5 vendor name in the list, from row1 to row5. In row 6, I want to enter the number of destinct vendor name in row6. See below for example. I don't know if there is a function for this in excel. I would also like to do this in code especially, because it will make things much easier. Any ideas or help will be grately appriciated. Thanks. A 1 TTMI 2 VERIZON 3 ZAYO 4 TTMI 5 ZAYO --------------- 6 3 VENDORS |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting items in one column based on criteria in another column | Excel Worksheet Functions | |||
| Counting number of text items in list | Excel Discussion (Misc queries) | |||
| Counting number of text items in list | Excel Discussion (Misc queries) | |||
| Counting number of items being added in a formula | Excel Discussion (Misc queries) | |||
| counting number of particular items in a list | Excel Discussion (Misc queries) | |||
Linear Mode
