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

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

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