+ Reply to Thread
Results 1 to 5 of 5

Counting unique items in a list based on a condition

  1. #1
    George Lynch
    Guest

    Counting unique items in a list based on a condition

    I need to count unique names in a list, but only when the unique name meets a
    condition (a specific title, for example) in another column. So let's say I
    have my list of names (many repeats) in B19:B224, and my list of titles in
    D19:D224. What I need to do is to count the unique names in the A column
    where the title is "Director", or "Vice President", etc.

    I can count the unique names in the A column with this formula:

    =SUM(IF(FREQUENCY(MATCH($B$19:$B$224,$B$19:$B$224,0),MATCH($B$19:$B$224,$B$19:$B$224,0))>0,1))

    I have thus far been unable to figure out a formula that will allow me to
    count the unique names in the B column based on specific titles in the D
    column.

    Thanks in advance for any and all help...

    George

  2. #2
    Alok
    Guest

    RE: Counting unique items in a list based on a condition

    Hi George,
    Perhaps this formula will do the trick..
    Here the A19:A225 are the titles within which you are looking for the unique
    values

    =SUMPRODUCT(--(A19:A225="T1"),--((FREQUENCY(MATCH($A$19:$A$224 &
    B$19:$B$224,$A$19:$A$224 & $B$19:$B$224,0),MATCH($A$19:$A$224 &
    $B$19:$B$224,$A$19:$A$224 & $B$19:$B$224,0))>0)))

    Alok

    "George Lynch" wrote:

    > I need to count unique names in a list, but only when the unique name meets a
    > condition (a specific title, for example) in another column. So let's say I
    > have my list of names (many repeats) in B19:B224, and my list of titles in
    > D19:D224. What I need to do is to count the unique names in the A column
    > where the title is "Director", or "Vice President", etc.
    >
    > I can count the unique names in the A column with this formula:
    >
    > =SUM(IF(FREQUENCY(MATCH($B$19:$B$224,$B$19:$B$224,0),MATCH($B$19:$B$224,$B$19:$B$224,0))>0,1))
    >
    > I have thus far been unable to figure out a formula that will allow me to
    > count the unique names in the B column based on specific titles in the D
    > column.
    >
    > Thanks in advance for any and all help...
    >
    > George


  3. #3
    Gary's Student
    Guest

    RE: Counting unique items in a list based on a condition

    Have you tried a Pivot Table? It can give you a count of all unique names
    organized by title.
    --
    Gary's Student


    "George Lynch" wrote:

    > I need to count unique names in a list, but only when the unique name meets a
    > condition (a specific title, for example) in another column. So let's say I
    > have my list of names (many repeats) in B19:B224, and my list of titles in
    > D19:D224. What I need to do is to count the unique names in the A column
    > where the title is "Director", or "Vice President", etc.
    >
    > I can count the unique names in the A column with this formula:
    >
    > =SUM(IF(FREQUENCY(MATCH($B$19:$B$224,$B$19:$B$224,0),MATCH($B$19:$B$224,$B$19:$B$224,0))>0,1))
    >
    > I have thus far been unable to figure out a formula that will allow me to
    > count the unique names in the B column based on specific titles in the D
    > column.
    >
    > Thanks in advance for any and all help...
    >
    > George


  4. #4
    George Lynch
    Guest

    RE: Counting unique items in a list based on a condition

    Hi Gary's Student,

    I should have mentioned in my previous post that we have already tried pivot
    tables and they do work, but this drill is part of a larger model, and the
    pivot tables are too unwieldy for use in this project. That's why we're
    trying to construct the formula. If the formula returns an accurate result,
    that result will be picked up elsewhere in the model and used for other
    calculations.

    "Gary's Student" wrote:

    > Have you tried a Pivot Table? It can give you a count of all unique names
    > organized by title.
    > --
    > Gary's Student
    >
    >
    > "George Lynch" wrote:
    >
    > > I need to count unique names in a list, but only when the unique name meets a
    > > condition (a specific title, for example) in another column. So let's say I
    > > have my list of names (many repeats) in B19:B224, and my list of titles in
    > > D19:D224. What I need to do is to count the unique names in the A column
    > > where the title is "Director", or "Vice President", etc.
    > >
    > > I can count the unique names in the A column with this formula:
    > >
    > > =SUM(IF(FREQUENCY(MATCH($B$19:$B$224,$B$19:$B$224,0),MATCH($B$19:$B$224,$B$19:$B$224,0))>0,1))
    > >
    > > I have thus far been unable to figure out a formula that will allow me to
    > > count the unique names in the B column based on specific titles in the D
    > > column.
    > >
    > > Thanks in advance for any and all help...
    > >
    > > George


  5. #5
    George Lynch
    Guest

    RE: Counting unique items in a list based on a condition

    Hi Alok,

    I'm a little confused by your formula; perhaps you can help me out. First,
    I assume the T1 reference in the formula is where you entered the actual
    title. I am curious why you enclosed that reference in quotes.

    Also, I adjusted the cell references to the actual cells in my model. The
    names are in B19:B224 and the titles are in D19:D224. But when I enter the
    formula, whether normally or as an array, I get the #VALUE error.

    I also don't understand the two dashes — or minus signs — after the
    SUMPRODUCT function and in front of the FREQUENCY function.

    Finally, I'm also not sure how the SUMPRODUCT works in this case. That is,
    I know how to use the SUMPRODUCT to get a result based on conditions, but I
    don't know how it's working here.

    I really appreciate your help with this and I look forward to your response.

    Thanks...

    George

    "Alok" wrote:

    > Hi George,
    > Perhaps this formula will do the trick..
    > Here the A19:A225 are the titles within which you are looking for the unique
    > values
    >
    > =SUMPRODUCT(--(A19:A225="T1"),--((FREQUENCY(MATCH($A$19:$A$224 &
    > B$19:$B$224,$A$19:$A$224 & $B$19:$B$224,0),MATCH($A$19:$A$224 &
    > $B$19:$B$224,$A$19:$A$224 & $B$19:$B$224,0))>0)))
    >
    > Alok
    >
    > "George Lynch" wrote:
    >
    > > I need to count unique names in a list, but only when the unique name meets a
    > > condition (a specific title, for example) in another column. So let's say I
    > > have my list of names (many repeats) in B19:B224, and my list of titles in
    > > D19:D224. What I need to do is to count the unique names in the A column
    > > where the title is "Director", or "Vice President", etc.
    > >
    > > I can count the unique names in the A column with this formula:
    > >
    > > =SUM(IF(FREQUENCY(MATCH($B$19:$B$224,$B$19:$B$224,0),MATCH($B$19:$B$224,$B$19:$B$224,0))>0,1))
    > >
    > > I have thus far been unable to figure out a formula that will allow me to
    > > count the unique names in the B column based on specific titles in the D
    > > column.
    > >
    > > Thanks in advance for any and all help...
    > >
    > > George


+ 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