Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Michael
 
Posts: n/a
Default 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?

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

=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?



  #3   Report Post  
Michael
 
Posts: n/a
Default

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?




  #4   Report Post  
Michael
 
Posts: n/a
Default

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?




  #5   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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


  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default

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



  #7   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"COUNTU" function in Excel to count unique entries in a range WayneL Excel Worksheet Functions 20 September 19th 08 03:50 AM
Count Unique Values annie Excel Worksheet Functions 1 June 9th 05 07:19 AM
Count Unique in Pivot Table Nelson Excel Discussion (Misc queries) 6 April 10th 05 10:05 PM
count duplicate (or, inversely, unique) entries, but based on a condition markx Excel Worksheet Functions 3 March 8th 05 07:57 PM
compare unique identifiers in multiple ranges bawilli_91125 Charts and Charting in Excel 1 November 30th 04 07:34 PM


All times are GMT +1. The time now is 05:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"