count frequency - formula amendment required

hi, appreciate any assistance you can provide :-)

I want to amend this array formula: {=COUNT(1/FREQUENCY(IF('Sheet1'!$B$2:$B$666=Sheet2!A4,IF('Sheet1'!$AQ$2:$AQ$666<>"",'Sheet1'!$AQ$2:$AQ$666)),'Sheet2'!$AQ$2:$AQ$666))}

It is saying IF a customer name on Sheet1 range B2:B666 = Customer name on Sheet 2 A4. return the count of a time stamp in column AQ and ignore duplicate time stamps. So if "Customer A" placed one order but the order contains several line items all with the same time stamp the formula counts 1 order per "Customer A" unique time stamp. and then looks at another time stamp and counts 2 orders for "Customer A"

But I need the formula to include another criteria which looks at the status of the order in column AI. There are various stages in the orders and I want the formula to only look at "Acknowledged", "Deployed" or "Processed" orders and then count the unique timestamps ignoring duplicates.

I have formatted the time stamps for the above formula to work, formatted from "23/12/15 10:54:42 (GMT)" to "105442" to use as the unique identifier and for the formula to work.. If some expert on here wants to amend the formula so that it also considers the original time stamp format too, that would be awesome!

I don't have excel on this computer and was not able to provide an example, I hope my description above is clear enough for someone to work some magic :-)


Thank you

thought I should point out that there are multiple customers I am searching against, I did not make this clear in my post.

thanks

thanks for your help - I tried this and it came up with the standard error message saying We found a problem with this formula. and it highlights the two quotation marks ie <>"" in the formula :-)

i'll upload an example within the hour :-) THanks a lot for your help

this variation looks good :-) appreciate the help from you both! perhaps I did not enter either correctly but I have created an example file

Originally Posted by FDibbins

maybe another IF...
{=COUNT(1/FREQUENCY(IF('Sheet1'!$B$2:$B$666=Sheet2!A4,IF('Sheet1'!$AQ$2:$AQ$666<>"",if('Sheet1'!$ai$2:$ai$666="Acknowledged",'Sheet1'!$AQ$2:$AQ$666))),'Sheet2'!$AQ$2:$AQ$666))}

I think you will need to upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Thank you this worked perfectly, I have added the other criteria. Thank you very much for all your help and to you newdoverman