+ Reply to Thread
Results 1 to 11 of 11

count frequency - formula amendment required

  1. #1
    Registered User
    Join Date
    12-30-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    49

    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. #2
    Registered User
    Join Date
    12-30-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    49

    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. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    12-30-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    49

    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. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    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. #6
    Registered User
    Join Date
    12-30-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    49

    Re: count frequency - formula amendment required

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

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: count frequency - formula amendment required

    Maybe a variation of this will work for you.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  8. #8
    Registered User
    Join Date
    12-30-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    49

    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
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-30-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    49

    Re: count frequency - formula amendment required

    Quote Originally Posted by FDibbins View Post
    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. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    Please Login or Register  to view this content.

    Entered with Ctrl + Shift + Enter
    Last edited by newdoverman; 12-18-2015 at 12:25 PM.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: count frequency - formula amendment required

    Glad it worked for you, thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SUM FREQUENCY formula to count distinct values w/ OR condition
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2015, 03:55 PM
  2. [SOLVED] If Frequency Formula count based on date is off by 1
    By antexity in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-07-2015, 01:30 PM
  3. count unique (frequency formula)
    By jw01 in forum Excel General
    Replies: 1
    Last Post: 01-12-2015, 10:00 PM
  4. [SOLVED] Sum & Count formula required
    By nagesh.tvsr in forum Excel Charting & Pivots
    Replies: 17
    Last Post: 09-29-2014, 05:59 AM
  5. [SOLVED] Count using date and other criteria - formula amendment
    By Christopherdj in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-02-2014, 07:37 PM
  6. Macro Amendment Required For Calculation Code
    By JimmiOO in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-27-2010, 08:16 AM
  7. Replies: 3
    Last Post: 11-05-2009, 10:17 AM

Bookmarks

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