how to count unique entries with multiple condition
 

A B
china P1
korea P2
japan P3
china P2
korea P2
japan P1
U.S P2
India P1
China P1
U.S P2

I'm working with about 8000 data, I try to used this formula
=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1: A10,A1:A10,0))0,1)) to
count the unique entries in column A, which result 5. (China,
japan,korea,india,U.S)

However, i want to count unique entries in, let say P1 only, which should be
3. How to use function to get this result?

=SUM(IF(FREQUENCY(IF((A1:A10<"")*(B1:B10="P1"),MA TCH(A1:A10,A1:A10,0)),ROW(
INDIRECT("1:"&ROWS(A1:A10))))0,1))

as an array formula, commit with Ctrl-Shift-Enter


--
HTH

Bob Phillips

"Michael" wrote in message
...
A B
china P1
korea P2
japan P3
china P2
korea P2
japan P1
U.S P2
India P1
China P1
U.S P2

I'm working with about 8000 data, I try to used this formula
=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1: A10,A1:A10,0))0,1)) to
count the unique entries in column A, which result 5. (China,
japan,korea,india,U.S)

However, i want to count unique entries in, let say P1 only, which should
be
3. How to use function to get this result?

Thanks! It works!

"Bob Phillips" wrote:

=SUM(IF(FREQUENCY(IF((A1:A10<"")*(B1:B10="P1"),MA TCH(A1:A10,A1:A10,0)),ROW(
INDIRECT("1:"&ROWS(A1:A10))))0,1))

as an array formula, commit with Ctrl-Shift-Enter


--
HTH

Bob Phillips

"Michael" wrote in message
...
A B
china P1
korea P2
japan P3
china P2
korea P2
japan P1
U.S P2
India P1
China P1
U.S P2

I'm working with about 8000 data, I try to used this formula
=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1: A10,A1:A10,0))0,1)) to
count the unique entries in column A, which result 5. (China,
japan,korea,india,U.S)

However, i want to count unique entries in, let say P1 only, which should
be
3. How to use function to get this result?

A B
china P1-C40907
korea P2
japan P3
china P2
korea P2
japan P1-C40609
U.S P2
India P1
China P1
U.S P2

If my data contains like "P1-C40XXX", and i want to include it in the
counting. How to achieve it? i've tried
=SUM(IF(FREQUENCY(IF((A1:A10<"")*(B1:B10="P1")*(B 1:B10="P1-C40???),MATCH(A1:A10,A1:A10,0)),ROW(INDIRECT("1:"& ROWS(A1:A10))))0,1))

but it doesn't work. Bob, can you help me?

"Michael" wrote:

Thanks! It works!

"Bob Phillips" wrote:

=SUM(IF(FREQUENCY(IF((A1:A10<"")*(B1:B10="P1"),MA TCH(A1:A10,A1:A10,0)),ROW(
INDIRECT("1:"&ROWS(A1:A10))))0,1))

as an array formula, commit with Ctrl-Shift-Enter


--
HTH

Bob Phillips

"Michael" wrote in message
...
A B
china P1
korea P2
japan P3
china P2
korea P2
japan P1
U.S P2
India P1
China P1
U.S P2

I'm working with about 8000 data, I try to used this formula
=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1: A10,A1:A10,0))0,1)) to
count the unique entries in column A, which result 5. (China,
japan,korea,india,U.S)

However, i want to count unique entries in, let say P1 only, which should
be
3. How to use function to get this result?

On Tue, 28 Jun 2005 07:39:10 -0700, "Michael"
wrote:

If my data contains like "P1-C40XXX", and i want to include it in the
counting. How to achieve it?

Try the array formula:

=SUM(IF(FREQUENCY(IF((A1:A10<"")*((B1:B10="P1")+
(LEFT(B1:B10,6)="P1-C40")),MATCH(A1:A10,A1:A10,0)),
ROW(INDIRECT("1:"&ROWS(A1:A10))))0,1))


--ron

Michael,

I read this that you want to include anything starting with P1 in the P1
numbers, so a slight change to Ron's suggestion

=SUM(IF(FREQUENCY(IF((A1:A10<"")*(LEFT(B1:B10,2)= "P1"),MATCH(A1:A10,A1:A10,
0)),ROW(INDIRECT("1:"&ROWS(A1:A10))))0,1))

still an array formula.

--
HTH

Bob Phillips

"Ron Rosenfeld" wrote in message
...
On Tue, 28 Jun 2005 07:39:10 -0700, "Michael"
wrote:

If my data contains like "P1-C40XXX", and i want to include it in the
counting. How to achieve it?

Try the array formula:

=SUM(IF(FREQUENCY(IF((A1:A10<"")*((B1:B10="P1")+
(LEFT(B1:B10,6)="P1-C40")),MATCH(A1:A10,A1:A10,0)),
ROW(INDIRECT("1:"&ROWS(A1:A10))))0,1))


--ron

On Wed, 29 Jun 2005 10:23:55 +0100, "Bob Phillips"
wrote:

Michael,

I read this that you want to include anything starting with P1 in the P1
numbers, so a slight change to Ron's suggestion

=SUM(IF(FREQUENCY(IF((A1:A10<"")*(LEFT(B1:B10,2) ="P1"),MATCH(A1:A10,A1:A10,
0)),ROW(INDIRECT("1:"&ROWS(A1:A10))))0,1))

still an array formula.

I read it that way at first; then when I went back and looked at his attempt,
with the P1-C40???, I thought my initial interpretation was incorrect.

Now he has several options.

--ron