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), )
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: