Unique values in column based on condition in another column

[emm8080]

Soemone else has asked this question recently - is there anyone who ca
please help???!!

I want to count the number of unique values in a column
of data where the cell value in another column has
a certain value.

i.e. Count Unique Values In Column A where Column B
= "House"

A B
3 House
2 Hotel
5 House
3 House

= 2

Thanks

Emm

 

[Frank Kabel]

Hi
four conditional counting unique entries try the following
array formula (entered with CTRL+SHIFT+ENTER):
=SUM(IF(FREQUENCY(IF((B1:B10="House"),MATCH
(A1:A10,A1:A10,0),""),IF((B1:B10="House"),MATCH
(A1:A10,A1:A10,0),""))>0,1))

I think Harlan posted this once to a similar question

 

[emm8080]

Hi

Thanks for the speedy response! I have a spreadsheet of hotels an
their visitors. Each hotel has a unique reference number - there are
different hotels named "Blue Hotel" and Mrs Jones has visited the
both.

Ref Hotel Visitor
A B C
1234 Black Hotel Mr Smith
1800 Blue Hotel Mrs Jones
1800 Blue Hotel Mrs Jones
1800 Blue Hotel Mrs Woods
2567 Blue Hotel Mrs Jones

I want to be able to create a new worksheet listing each hotel (once
and the number of unique visitors that hotel has had. I will enter th
ref and hotel name manually and then I want to be able to "fetch" th
Unique Visitors value.

Ref Hotel Uniques
G H I
1234 Black Hotel 1
1800 Blue Hotel 2
2567 Blue Hotel 1

The coding you suggested does work with "house" as the value, but
would like it to work using cell address eg if G1 = A1:A4.....

Hope you can follow this.

Thanks

Emm

 

[Frank Kabel]

Hi
just replace "house" with your cell reference. e.g.
=SUM(IF(FREQUENCY(IF((B1:B10=G1),MATCH
(A1:A10,A1:A10,0),""),IF((B1:B10=G1),MATCH
(A1:A10,A1:A10,0),""))>0,1))