+ Reply to Thread
Results 1 to 4 of 4

Count Unique Values Based on citeria in Two Other Columns

  1. #1
    Registered User
    Join Date
    07-17-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Count Unique Values Based on citeria in Two Other Columns

    I have a data set as follows:

    Colmn B Colmn C Colmn D Colmn G
    Test Location Instrument Reading
    1 1 1 1
    1 1 1 2
    1 1 1 3
    1 2 3 1
    1 2 3 2
    2 4 1 1
    2 4 1 2
    2 6 2 1
    3 1 1 1

    I want to know the number of unique tests based solely on location and instrument (Not including reading). In the example above, there would be 5 unique tests (1,1,1; 1,2,3; 2,4,1; 2,6,2; 3,1,1). I'm looking to display the data in a table format where Location and Instrument are the Row and Column Header, and the values within each cell are the number of unique tests. For instance:

    Inst
    1 2 3
    Location: 1 2
    2 1
    4 1
    6 1

    I'm envisioning a forumla where I can set the "location" and "instrument" dependent on the value in the "final table" and have it sort through the data. Thus, there would be the same forumla repeated a number of times.

    Any help would be appreciated.

  2. #2
    Registered User
    Join Date
    07-17-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Count Unique Values Based on citeria in Two Other Columns

    It appears that the formatting didn't work. I attached an example file
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Count Unique Values Based on citeria in Two Other Columns

    Try these;

    I6, and copy down.

    =IF(MAX(I$5:I5)=MAX(C:C),"",SMALL(C:C,1+COUNTIF(C:C,"<="&I5)))

    J5, copy across

    =IF(MAX($I5:I5)=MAX($D:$D),"",SMALL($D:$D,1+COUNTIF($D:$D,"<="&I5)))

    J6, with CTRL+SHIFT+ENTER

    =SUM(IF(FREQUENCY(IF($C$5:$C$100=$I6,IF($D$5:$D$100=J$5,$B$5:$B$100)),$B$5:$B$100),1))

    Then copy J6 & paste down & across. Adjust the range.
    Last edited by Haseeb Avarakkan; 07-17-2012 at 05:56 PM.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  4. #4
    Registered User
    Join Date
    07-17-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Count Unique Values Based on citeria in Two Other Columns

    Awesome, that is exaclty what I was looking for. Thanks!

+ 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