+ Reply to Thread
Results 1 to 5 of 5

COUNT unique reference numbers based dates in a specified date range

  1. #1
    Registered User
    Join Date
    08-14-2012
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    5

    COUNT unique reference numbers based dates in a specified date range

    I am trying to create a formula that auto-calculates a summary page and cannot work out how to create the formula.

    Essentially what I am trying to achieve is;

    1. Identify date entries within a specified date range
    2. Of those in the desired date range, I would like to use customer reference numbers to ensure that only unique entries are returned

    I think I need to use a COUNTIF formula but don't seem to make it work.

    I hope that makes sense, this is my first forum post!


    COUNTIF QUERY.xlsx

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: COUNT unique reference numbers based dates in a specified date range

    So columns K:O are counts of unique dates?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-14-2012
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: COUNT unique reference numbers based dates in a specified date range

    Yes, it will be multi-product so will need to return count based on customer reference numbers.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: COUNT unique reference numbers based dates in a specified date range

    This will count Interest dates between your date range for unique customers:

    =COUNT(1/FREQUENCY(IF($B$2:$B$16>=$I$3,IF($B$2:$B$16<=$J$3,IF($A$2:$A$16<>"",MATCH($A$2:$A$16,$A$2:$A$16,0)))),ROW($A$2:$A$16)-ROW($A$2)+1))

    confirmed with CTRL+SHIFT+ENTER not just ENTER.

    adjust the B2:B16 range for other date columns...

  5. #5
    Registered User
    Join Date
    08-14-2012
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: COUNT unique reference numbers based dates in a specified date range

    Amazing! Thank you very much! ))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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