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

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

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

=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

Assuming your data is in column a and b

Use =countif(b1:b8,"Phoenix) in column b.


"Mark O" wrote:

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

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

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 vendors and one with the city. I am trying to count how many unique
vendors are in Phoenix (or which ever city I choose in the drop down).


"E10" wrote:

Assuming your data is in column a and b

Use =countif(b1:b8,"Phoenix) in column b.


"Mark O" wrote:

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

Assuming C1 is your "dropdown" and A2:B25 your table, try this ARRAY FORMULA
(Commit with Ctrl-Shift-Enter):

=COUNT( 1 / ( MATCH( $A$2:$A$25, IF( $B$2:$B$25 = $C$1, $A$2:$A$25, #N/A ),
0 ) = ( ROW( $A$2:$A$25 ) - ROW( $A$2 ) + 1 ) ) )

Adjust to your ranges...

--
Regards,
Luc.

"Festina Lente"


"Mark O" wrote:

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

=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