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

ExcelPain

New member
Joined
Apr 7, 2015
Messages
4
Reaction score
0
Points
0
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:
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))
 
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.
 
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))
 
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.
 
*** 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))
 
Back
Top