+ Reply to Thread
Results 1 to 3 of 3

Unique values in a column with duplicates

  1. #1
    Registered User
    Join Date
    11-09-2008
    Location
    Eastern Time (NY)
    MS-Off Ver
    Home and Business Office 2016
    Posts
    13

    Unique values in a column with duplicates

    If sheet2 cols A and C equal the value in sheet1 cols H and I, I want to return the number of unique values in sheet 1 column A. Can anyone provide a fiormula for accomplishing this:

    The following example should yield a result of 2 for the Arenas/Bibby combination because there are two rows in sheet1 where Arenas is in colH and Bibby in colI, AND the date in colA is unique (10/29/08 and 10/30/08):

    sheet2
    colA colC
    Arenas Bibby


    Please Login or Register  to view this content.

    Thanks,
    Bob
    Last edited by shg; 11-09-2008 at 02:09 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    In sheet 2 D2 try this formula

    =SUM(IF(FREQUENCY(IF(Sheet1!H$1:H$10=A2,IF(Sheet1!I$1:I$10=C2,IF(Sheet1!A$1:A$10<>"",MATCH(Sheet1!A$1:A$10,Sheet1!A$1:A$10,0)))),ROW(Sheet1!A$1:A$10)-ROW(Sheet1!A$1)+1),1))

    It's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531
    daddylonglegs beat me to it and with a more elegant solution (shocker :-)) but if you wanted to add a helper on sheet 1 column J you could use standard SUMPRODUCT... eg

    Sheet1!J1:
    =A1&":"&H1&":"&I1

    copied down for all rows say to row 100

    Then Sheet2 formula:
    =SUMPRODUCT(--(ISNUMBER(SEARCH(A1&":"&C1,Sheet1!$J$1:$J$100))),--(1/COUNTIF(Sheet1!$J$1:$J$100,Sheet1!$J$1:$J$100)))

+ 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