+ Reply to Thread
Results 1 to 5 of 5

Count Unique Values for a Specific Date

  1. #1
    Registered User
    Join Date
    06-07-2020
    Location
    England
    MS-Off Ver
    Home and Office 2013
    Posts
    1

    Count Unique Values for a Specific Date

    Hi,

    I've been searching for hours trying to find a solution to my problem even though it seems fairly simple. I apologise if it is and I'm being dumb!

    I would like to count the amount of unique values (order numbers) in a table that correlates with a specific date. For example, how many unique orders were made on 07/06/20. Each order may have multiple lines so a simple COUNT function wouldn't work. I have attached an example set of data. I have found lots of tutorials with SUMPRODUCT and 1/COUNTIF, which works apart from it gives a #DIV/0! error when you expand the range beyond the data. As this is an order list, I am adding tens of lines of data everyday and would like the formula to have a range of A2:A5000 or higher so it automatically updates whenever I add new orders to the main list.

    I have a seperate sheet with all of the dates in column A and would like the number of unique order numbers in column B. Help please!
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Count Unique Values for a Specific Date

    Hi, welcome to the forum.
    Who's dumb? Nobody is dumb. Sometimes we don't know the anser but that doesn't make one dumb, so don't apologize
    If you have no questions left over to ask that you're thorough learning. In that case go buy 6 planks and ...

    Well, now bak to your question.
    I have not yet looked at your file so do not know why you the column B but it you want a list of unique values from a column you can use the Advanced Filter menu option in the Ribbon
    It permits you to copy a column's unique values to another location.

    Give that a try and see if it can help you, in the meantime I'll download your file and see if there is a simple formula to accomplish that, I'm not that great with formulas but maybe I'll see the solution somewhere
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Count Unique Values for a Specific Date

    I added a third column that counts the order nr per date
    Will this help?
    In Column K is the result of the advanced filter I mentioned before
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Count Unique Values for a Specific Date

    A
    B
    C
    D
    E
    F
    G
    1
    Date Invoice Date
    Unique Orders
    2
    2/21/2020
    15567
    2/21/2020
    4
    3
    2/21/2020
    15567
    2/22/2020
    1
    4
    2/21/2020
    15567
    2/23/2020
    2
    5
    2/21/2020
    55978
    2/24/2020
    3
    6
    2/21/2020
    35013
    2/25/2020
    5
    7
    2/21/2020
    67922
    8
    2/22/2020
    15620
    9
    2/23/2020
    73486
    10
    2/23/2020
    17106
    11
    2/24/2020
    97625
    12
    2/24/2020
    97625
    13
    2/24/2020
    8452
    14
    2/24/2020
    15747
    15
    2/24/2020
    15747
    16
    2/24/2020
    15747
    17
    2/25/2020
    15808
    18
    2/25/2020
    15767
    19
    2/25/2020
    68733
    20
    2/25/2020
    68733
    21
    2/25/2020
    68733
    22
    2/25/2020
    15801
    23
    2/25/2020
    15801
    24
    2/25/2020
    15801
    25
    2/25/2020
    15813


    I changed invoice from text to number

    G2=IF($F2<>"",SUM(IF(FREQUENCY(IF($A$2:$A$100<>"",IF($A$2:$A$100=$F2,$B$2:$B$100)),$B$2:$B$100),1)),"")

    Control+shif+enter

    copy down

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Count Unique Values for a Specific Date

    @CARACALLA: nice one, I placed it the worksheet I had uploaded so show it and it does the job

+ 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] How to count unique names on specific date for daily report?
    By specky_ in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-27-2019, 09:14 AM
  2. Count Unique Names across multiple columns for specific date
    By KattieSpencer in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-22-2016, 10:07 PM
  3. [SOLVED] Count unique values on a specific date
    By Even in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-21-2014, 02:05 PM
  4. Count unique values within a range that meet a specific condition
    By CSS in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-24-2013, 02:00 PM
  5. Count unique values with specific criteria
    By kaz_jones in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2012, 03:56 PM
  6. Replies: 0
    Last Post: 08-09-2010, 02:09 PM
  7. Count unique records for a specific date
    By Mallycat in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-22-2010, 07:13 AM

Tags for this Thread

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