Results 1 to 7 of 7

Thread: trying to get unique count with multiple selection and that works with filtering

  1. #1

    trying to get unique count with multiple selection and that works with filtering



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi - I'm not very experienced with excel and this is my first post but I have the need for the following.

    I have two columns I am trying to get information from. The first column contains an error type like LFR, RTR etc and the other column contains an order number. I have about twenty columns that can be filtered so this calc will need to reflect counts on the filtered data.

    I am trying to select code RTR from the error type column and bypass the ZZZ entries and get a unique count of number of orders from the Order# column . There will be multiple combinations of error type and order. See below. I can't use a pivot table because columns are being filtered and I need the value returned in a cell that will feed a chart.

    $A9 = RTR, Column Y is order#.

    Error type Order#
    RTR ZZZ E 761927
    RTR ZZZ E 761927
    RTR ZZZ E 776984
    RTR ZZZ E 780912
    LFR ZZZ E 780912
    RTR ZZZ E 780912
    RTR ZZZ E 780912
    RTR ZZZ E 780912
    RER ZZZ E 780912
    RTR ZZZ E 780912
    RTR ZZZ E 783521

    I have tried all kinds of combinations of formulas but can't get the right numbers. I would really appreciate some help. Thank you.
    This is the closest I've got but it's not right.
    =SUM(IF(A23:A50000=$A9,IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(Y23,ROW(Y23:Y50000)-ROW(Y23),,1)),Y23:Y50000),Y23:Y50000),1)*1,))
    This give me the unique number but doesn't allow for any selection which is what the above formula is trying to accomplish.
    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(Y23,ROW(Y23:Y50000)-ROW(Y23),,1)),Y23:Y50000),Y23:Y50000),1), )
    Last edited by ExcelPain; 2015-04-07 at 03:18 PM. Reason: additional info

  2. #2
    Thius works

    =SUM(--(FREQUENCY(IF($A$23:$A$50000=$A$9,MATCH($Y$23:$Y$50000,$Y$23:$Y$50000,0)),ROW(INDIRECT("1:"&ROWS($Y$23:$Y$50000))))>0))

  3. #3
    Thank you for the reply. It works but unfortunately it includes all rows and not just filtered rows. This has been my biggest problem getting this to work. It seems only subtotal works for filtered rows from what I've experienced and read.

  4. #4
    Okay, I thought you wanted it the other way.

    You need to include the condition withing FREQUENCY, as in this version

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(Y23,ROW(Y23:Y50000)-ROW(Y23),,1)),
    IF(A23:A50000=A9,MATCH("~"&Y23:Y50000,Y23:Y50000&"",0))),ROW(Y23:Y50000)-ROW(Y23)+1),1))

  5. #5
    This works correctly and not complaining but is slow with this many rows. It takes 30 seconds to refresh with the number of unique orders. Users will complain but might have to deal with it if it's the only way to do this. Thank you for your help.

  6. #6
    Excel 2010 with free PowerPivot Add-In.
    Compatible with Office 2013 Pro Plus.
    For all the other complainers.
    Fast.
    http://www.mediafire.com/view/6bgofd.../04_07_15.xlsx

  7. #7
    *** ANSWER *** For anyone else needing this function here is another suggestion from Mr. Excel forum that works and is pretty fast. Thanks to all that helped me. =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(Y23,ROW(Y23:Y50000)-ROW(Y23),,1)), IF(A23:A50000="RTR",IF(B23:B50000<>"ZZZ",Y23:Y50000))),Y23:Y50000),1))

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •