+ Reply to Thread
Results 1 to 19 of 19

how to count unique entries with multiple condition

  1. #1
    Ron Rosenfeld
    Guest

    Re: how to count unique entries with multiple condition

    On Wed, 29 Jun 2005 10:23:55 +0100, "Bob Phillips" <[email protected]>
    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

  2. #2
    Bob Phillips
    Guest

    Re: how to count unique entries with multiple condition

    =SUM(IF(FREQUENCY(IF((A1:A10<>"")*(B1:B10="P1"),MATCH(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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Bob Phillips
    Guest

    Re: how to count unique entries with multiple condition

    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" <[email protected]> wrote in message
    news:[email protected]...
    > On Tue, 28 Jun 2005 07:39:10 -0700, "Michael"
    > <[email protected]> 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




  4. #4
    Michael
    Guest

    Re: how to count unique entries with multiple condition

    Thanks! It works!

    "Bob Phillips" wrote:

    > =SUM(IF(FREQUENCY(IF((A1:A10<>"")*(B1:B10="P1"),MATCH(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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #5
    Ron Rosenfeld
    Guest

    Re: how to count unique entries with multiple condition

    On Tue, 28 Jun 2005 07:39:10 -0700, "Michael"
    <[email protected]> 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. #6
    Michael
    Guest

    Re: how to count unique entries with multiple condition

    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")*(B1: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"),MATCH(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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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?
    > > >

    > >
    > >
    > >


  7. #7
    Michael
    Guest

    Re: how to count unique entries with multiple condition

    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")*(B1: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"),MATCH(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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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?
    > > >

    > >
    > >
    > >


  8. #8
    Ron Rosenfeld
    Guest

    Re: how to count unique entries with multiple condition

    On Wed, 29 Jun 2005 10:23:55 +0100, "Bob Phillips" <[email protected]>
    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

  9. #9
    Bob Phillips
    Guest

    Re: how to count unique entries with multiple condition

    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" <[email protected]> wrote in message
    news:[email protected]...
    > On Tue, 28 Jun 2005 07:39:10 -0700, "Michael"
    > <[email protected]> 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




  10. #10
    Ron Rosenfeld
    Guest

    Re: how to count unique entries with multiple condition

    On Tue, 28 Jun 2005 07:39:10 -0700, "Michael"
    <[email protected]> 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

  11. #11
    Michael
    Guest

    Re: how to count unique entries with multiple condition

    Thanks! It works!

    "Bob Phillips" wrote:

    > =SUM(IF(FREQUENCY(IF((A1:A10<>"")*(B1:B10="P1"),MATCH(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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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?
    > >

    >
    >
    >


  12. #12
    Bob Phillips
    Guest

    Re: how to count unique entries with multiple condition

    =SUM(IF(FREQUENCY(IF((A1:A10<>"")*(B1:B10="P1"),MATCH(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" <[email protected]> wrote in message
    news:[email protected]...
    > 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?
    >




  13. #13
    Michael
    Guest

    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?


  14. #14
    Ron Rosenfeld
    Guest

    Re: how to count unique entries with multiple condition

    On Tue, 28 Jun 2005 07:39:10 -0700, "Michael"
    <[email protected]> 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

  15. #15
    Michael
    Guest

    Re: how to count unique entries with multiple condition

    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")*(B1: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"),MATCH(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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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?
    > > >

    > >
    > >
    > >


  16. #16
    Bob Phillips
    Guest

    Re: how to count unique entries with multiple condition

    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" <[email protected]> wrote in message
    news:[email protected]...
    > On Tue, 28 Jun 2005 07:39:10 -0700, "Michael"
    > <[email protected]> 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




  17. #17
    Michael
    Guest

    Re: how to count unique entries with multiple condition

    Thanks! It works!

    "Bob Phillips" wrote:

    > =SUM(IF(FREQUENCY(IF((A1:A10<>"")*(B1:B10="P1"),MATCH(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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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?
    > >

    >
    >
    >


  18. #18
    Ron Rosenfeld
    Guest

    Re: how to count unique entries with multiple condition

    On Wed, 29 Jun 2005 10:23:55 +0100, "Bob Phillips" <[email protected]>
    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

  19. #19
    Bob Phillips
    Guest

    Re: how to count unique entries with multiple condition

    =SUM(IF(FREQUENCY(IF((A1:A10<>"")*(B1:B10="P1"),MATCH(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" <[email protected]> wrote in message
    news:[email protected]...
    > 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?
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1