Count unique values with conditions

{ 13 Comments }

By Ashish Mathur · · , · Leave a comment

There is a three column database with month in column A, numbers in column B and names in column C.  I want to count unique numbers in column B for a specific month and name provided by the end user.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the PowerPivot add-in, then a simple DAX formula can solve this problem.

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system’s performance.

You may refer to both solutions in this workbook.

To count unique values with conditions on large databases, you may refer to the following link

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • simple, I just need count of y and n for the distinct names

    y = 4

    n= 2

    name status
    a y
    a y
    b y
    b n
    c y
    d y
    c n
    c n

    Reply

    • Hi,

      This will work in all versions of MS Excel

      1. Suppose your data is in range A2:B9
      2. In range A12:A13, type Y and N respectively
      3. In cell B12, enter this array formula (Ctrl+Shift+Enter) and copy to cell B13

      =SUMPRODUCT(1*(FREQUENCY(IF($B$2:$B$9=$A12,MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($B$2:$B$9)-ROW($A$1))>0))

      In Excel 2013, you may use the Pivot Table’s DISTINCTCOUNT feature. When creating the Pivot Table, simply click on “Add this Data to the Data Model”. Thereafter, under Value Field Settings, just select Distinct Count (last option).

      Hope this helps.

      Reply

  • can you please explain why we require Ctrl+Shift+Enter ?

    Later, I will implement this formula into macro, so that instead of typing manually the macro will do my work, like activecell.formulaR1C1 = “formula”
    during that time how to manage this Ctrl+Shift+Enter)
    Please advise…

    Reply

  • Have a complicated lookup or maybe match and count problem. I need to count the orders that either- 1: only come from fast moving items, 2: come from a combination of fast and slow moving and 3: come from only slow moving. The other issue is the orders are listed multiple times and only need to be counted once, so in the below data, order 3618658 has 4 lines and 4 different items that are all fast moving. But only need to count this as 1 order. Order 3618676 has 1 slow moving and 5 fast moving so it needs to be counted as 1 order in the fast and Slow category. Hope this is clear. Thanks Gene

    # of Orders from Fast Moving ONLY # of Orders from Fast and Slow Moving # of Orders from Slow Moving ONLY
    2 1 1

    ACTUAL ITEMS & ORDERS FAST MOVING SLOW MOVING
    Item Order # Item Item
    32062.15.M 3617203 32062.15.M 30364.515.M
    30717.811.M 3618658 30717.811.M 27913.816.M
    31458.11.M 3618658 31458.11.M 26358.819.M
    30191.811.M 3618658 30191.811.M 26063.516.M
    28366.106.M 3618658 28366.106.M
    25691.1610.M 3618676 25691.1610.M
    30364.619.M 3618676 30364.619.M
    29625.615.M 3618676 29625.615.M
    30170.424.M 3618676 30170.424.M
    26782.5.M 3618676 26782.5.M
    30364.515.M 3618676
    27913.816.M 3618731
    26358.819.M 3618731
    26063.516.M 3618731

    Reply

    • For the benefit of other, here is the link to the original post.

      So if the answer in cell B2 of sheet2 is 168, then you want to list all 168 unique ID’s from C2:FN2 of sheet2? If that be the case, then you may see if this solution works.

      Hope this helps.

      Reply

  • I need to total the DIFFERENT owed values within each matching first & last name and return that value to, ideally, the red circle – So for person AB the total owed would be $1000 but for person CD the total owed would be $1841…

    I also need to total all the paid values for each matching first & last name and return that value, ideally, to the purple circle –

    So for person AB the total paid would be $700 but for person CD the total paid would be $1641…

    Any and all help is greatly appreciated !!

    Original questions thread -http://answers.microsoft.com/en-us/office/forum/office_2010-excel/programming-in-excel/4fe80c0f-859d-4682-8add-635081561ca4?tm=1417465400947

    Thank you !

    Reply