=SUM(--(FREQUENCY(IF(B2:B100=C1,MATCH(A2:A100,A2:A100,0)) ,ROW(INDIRECT("1:"&ROWS(A2:A100))))0))

This is an array formula, so commit with Ctrl-Shift-Enter, and still assumes
the dropdown in C1.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Mark O" wrote in message
...
Bob, that won't quite work. I have an extra element to this you are
missing.
Your example would work if I was counting occurences of Phoenix only.
There
are two columns here. One with the city and one with vendors. I am then
trying to count how many unique vendors are in Phoenix (or which ever city
I
choose in the drop down).

"Bob Phillips" wrote:

=COUNTIF(B:B,C1)

where C1 is the dropdown box.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Mark O" wrote in message
...
I quite familiar with Pivot tables, and it would work, but I am hoping
to
create a drop down list for the city, which would allow a simple user
to
just
change the dropdown to find the information. So really would just like
a
single formula i can put in a cell next to the drop down.

"Bernard Liengme" wrote:

I think I would go for a Pivot Table. Takes a few experiments the
first
time
to get it right but it is a very powerful tool to know how to use.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Mark O" <Mark wrote in message
...
Looking for a formula where, using the data below, if I pick a city,
such
as
PHOENIX, it will count the number of unique vendors that city has.
So
this
example would give me the value of 4 (unique vendors A, B, C, and
D).


Vendor City
A PHOENIX
B PHOENIX
B PHOENIX
C PHOENIX
C PHOENIX
D PHOENIX
A LA
C CHICAGO