+ Reply to Thread
Results 1 to 6 of 6

Unique Total Value Count per Unique Lookup Values

  1. #1
    Registered User
    Join Date
    07-31-2014
    Location
    New York
    MS-Off Ver
    2013
    Posts
    4

    Unique Total Value Count per Unique Lookup Values

    Hello all, I have a perplexing problem that I cannot solve, and would like to know if anyone could help. My sample data is in the file at the bottom of this post.

    GIVEN:
    Each WO can have multiple Serials. These Serials can be unique, repeated, or both (for a certain WO). Serials may also repeat across different WOs.

    There are four categories for NC Info (a number, "NC Pending", "Need NC", and "No NC").

    FIND:
    Count the total number of unique Serials PER WO that meet the criteria of "No NC" in the NC Info column.

    EXAMPLE:
    • WO 1010 is listed 8 times in the WO # column.
    • There are four different serials (700, 720, 740, Ser-01) for WO 1010.
    • Serial 700 does not repeat FOR THIS UNIQUE WO. Serials 720, 740, and Ser-01 repeat FOR THIS UNIQUE WO.
    • Thus, the number of unique Serials in WO 1010 that have the NC Info of "No NC" is 4. Notice that Ser-01 also shows up for WO 1020 twice. So Ser-01 will be counted once for WO 1010, and once for WO 1020.

    I need to know the number of unique Serials (per WO) in total that meet the “No NC” criteria. Because some Serials may appear in more than one WO (where they may also repeat), the count for that Serial may or may not equal 1, but may increase if the Serial is in different WOs.

    MORE INFO:
    I have managed to use SUM(IF(FREQUENCY(IF… to successfully find the number of unique WOs that meet other NC Info categories, such as those with a number or those with “NC Pending”. Note that I was searching the WOs and not Serials. For example, to find all of the “NC Pending” WOs, I used:

    Please Login or Register  to view this content.
    Which returns the correct answer of 1 for my sample data.

    The “No NC” scenario is different. I now want to search the Serials. I have tried the following formula but it does not give the correct answer.

    Please Login or Register  to view this content.
    The result of this formula is 6. As you can tell from my sample data, the correct result should be 8 (4 unique Serials for WO 1010, 3 unique Serials for WO 1020, and 1 unique Serial for WO 1030).

    Thank you for your time and I would appreciate any help you can provide.
    Attached Files Attached Files
    Last edited by KnightVision; 08-01-2014 at 11:15 AM.

  2. #2
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Unique Total Value Count per Unique Lookup Values

    Provide a sample workbook..
    that will help us to help you better..


    Don't forget to click *

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Unique Total Value Count per Unique Lookup Values

    Provide a sample workbook..
    that will help us to help you better..


    Don't forget to click *

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Unique Total Value Count per Unique Lookup Values

    Hi,

    Perhaps an array formula**:

    =SUM(IFERROR((E3:E500="No NC")/COUNTIFS(A3:A500,A3:A500,C3:C500,C3:C500,E3:E500,"No NC"),0))

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    07-31-2014
    Location
    New York
    MS-Off Ver
    2013
    Posts
    4

    Re: Unique Total Value Count per Unique Lookup Values

    Thank you very much XOR LX, that formula does exactly what I needed!

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Unique Total Value Count per Unique Lookup Values

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Total count of unique Values
    By bremen22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-23-2013, 06:48 PM
  2. [SOLVED] Lookup Unique ID Then Count Values in Different Column
    By BoardGuy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-22-2013, 03:15 PM
  3. [SOLVED] Lookup/Count unique values
    By aikorei in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-16-2012, 01:43 PM
  4. total count of non-blank cell(s) and unique values
    By esanchezz in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-09-2010, 01:53 PM
  5. Replies: 17
    Last Post: 08-24-2009, 08:58 AM

Tags for this Thread

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