Complex Unique Count
Results 1 to 7 of 7

Thread: Complex Unique Count

  1. #1
    Board Regular
    Join Date
    Aug 2011
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Complex Unique Count

    Hi Guys,

    Is there a way to do the following:

    Count the unique Ref, Name and Date so the required results would show in the 'unique count' column. So the below is basically saying for example, Jim appears 5 times in the table with the same ref no but is on two different days, so essentially it is counting 1 for 01/06/19 and 1 for 02/06/19 but leaving the others blank as essentially they are duplicates. That wasy i can then use the Unique count column to sum the actual unique instances.

    I hope this makes sense


    dealer ServCode Tran1 Tran2 Tran3 OpNum RefNo Name Date Unique Count
    CA MOT IBB 527 1 Dave 01/06/19 1
    NSK BPOLLEN IBB 292 2 Beth 01/06/19 1
    NSK SER ENQ QCH 292 2 Beth 01/06/19
    CA SER ENQ COD 292 2 Beth 01/06/19
    MCH MEC CAN 292 3 Jim 01/06/19 1
    MCH MEC CAN 292 3 Jim 01/06/19
    MCH MEC CAN 292 3 Jim 01/06/19
    MCH MEC CAN 293 3 Jim 02/06/19 1
    MCH MEC CAN 294 3 Jim 02/06/19
    NSK MEC W3B QB 292 4 Jim 01/06/19 1

  2. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Complex Unique Count

    Hey,

    I think this should work:
    Code:
    IF(SUMPRODUCT(($G$2:G2=G2)*($H$2:H2=H2)*($I$2:I2=I2))=1,1,"")
    Assuming the Unique Count column is Col J and the first row used is the 2nd row.
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Complex Unique Count

    Another option
    =IF(COUNTIFS(H$2:H2,H2,G$2:G2,G2,I$2:I2,I2)=1,1,"")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Complex Unique Count

    Is possible to achieve the desired result, 5 in this case, without Unique Count column.

    Array formula
    =SUM(IF(FREQUENCY(MATCH(G2:G11&"|"&I2:I11,G2:G11&"|"&I2:I11,0),ROW(G2:G11)-ROW(G2)+1),1))
    Ctrl+Shift+Enter

    M.

  5. #5
    Board Regular
    Join Date
    Aug 2011
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex Unique Count

    Thank you everyone this is amazing.

  6. #6
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Complex Unique Count

    No problem, glad that we could help!
    √-1 2³ ∑ π
    …And it was delicious!

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Complex Unique Count

    You're welcome
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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