Share via

Counting unique alphanumeric values with two criteria

Anonymous
2017-06-29T14:12:24+00:00

Hi People, I have huge data set of 20k lines. Attached is the image with an example data structure and output I am trying to achieve.

Problem statement: Count of unique "materials" received in "calendar week" of "specific Type" used Combination of SUM, IF, FREQUENCY, MATCH and ROW functions. but my end result was #VALUE error. Any suggestions on how to solve this is much appreciated. Thanks a lot

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-06-29T16:07:01+00:00

    I played about with the formula often used to count unique values

    =SUM(IF(FREQUENCY(MATCH(B2:B11,B2:B11,0),MATCH(B2:B11,B2:B11,0))>0,1))

    trying to add type must match and cw must match but no luck

    I resorted to helper columns I and J

    Note I had to replace the blank type by 0 in the data

    best wishes

    0 comments
  2. Ashish Mathur 101K Reputation points Volunteer Moderator
    2017-06-29T23:48:14+00:00

    Hi,

    Try this

    1. in the Type column, type 0 in place of blank cells
    2. Select the entire dataset and go to Insert > Pivot Tables.  Check the box there for "Add this data to the Data Model" > OK
    3. Drag Type to Row labels and CW to column labels
    4. Drag Material to the value area section
    5. Right click on any number in the value area section and go to Summarise value By > More Options.  Scroll to the bottom and select Distinct Count > OK

    Hope this helps.

    0 comments