# count frequency - formula amendment required

1. ## 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

2. ## Re: count frequency - formula amendment required

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

thanks

3. ## Re: count frequency - formula amendment required

Obviously untested, but perhaps...
{=COUNT(1/FREQUENCY(IF('Sheet1'!\$B\$2:\$B\$666=Sheet2!A4,IF(('Sheet1'!\$AQ\$2:\$AQ\$666<>"")*('Sheet1'!\$ai\$2:\$ai\$666="Acknowledged"),'Sheet1'!\$AQ\$2:\$AQ\$666)),'Sheet2'!\$AQ\$2:\$AQ\$666))}

CSE

I know you want others, but lets see if this works 1st

4. ## Re: count frequency - formula amendment required

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

5. ## Re: count frequency - formula amendment required

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.

6. ## Re: count frequency - formula amendment required

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

7. ## Re: count frequency - formula amendment required

Maybe a variation of this will work for you.
Formula:
`Please Login or Register  to view this content.`

8. ## Re: count frequency - formula amendment required

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

9. ## Re: count frequency - formula amendment required

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

10. ## Re: count frequency - formula amendment required

For those curious, this is what works with the workbook and I think is the formula that Nubian finally arrived at:
Formula:
`Please Login or Register  to view this content.`

Entered with Ctrl + Shift + Enter

11. ## Re: count frequency - formula amendment required

Glad it worked for you, thanks for the feedback

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1