Help with counting the number of destinct items in a column

A

Ayo

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
 
J

Jacob Skaria

'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
 
A

Ayo

Thanks Jacob. Works great.

Jacob Skaria said:
'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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Mail Merge Help 1
Group rows based on indention of string in a column 0
Setting column width in a table with a macro is not working 0
clear cells containing specific values 7
How to center adjust text above and image in a two column document. 1
Template with fields and premade texts.. 0
Need help removing extra TOC text from number lists used as TOC levels 0
Excel - Wich Function should I use? 1

Top