+ Reply to Thread
Results 1 to 8 of 8

Count of Unique Values Based on Date in Another Column

  1. #1
    Registered User
    Join Date
    02-08-2013
    Location
    toronto, ON
    MS-Off Ver
    Excel 2010
    Posts
    10

    Count of Unique Values Based on Date in Another Column

    Hi! Below is the sample data:

    Column A Column B
    Date Registration #
    2014-03-06 308011
    2014-03-06 308011
    2014-03-06 308035
    2014-03-06 308047
    2014-03-07 308011
    2014-03-07 308035
    2014-03-07 308035
    2014-03-07 308035
    2014-03-08 308011
    2014-03-08 308035
    2014-03-08 308047
    2014-03-08 308047



    I need total new registrations on each day (Unique values only) as below:

    Date Total New Registration
    2014-03-06 3
    2014-03-07 2
    2014-03-08 3

    I have tried different formulas but can't seem to get the desired result. Any help would be appreciated!

  2. #2
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: Count of Unique Values Based on Date in Another Column

    Tried pivot table, have a look.

    Attached Files Attached Files

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Count of Unique Values Based on Date in Another Column

    Try array formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files
    Best Regards,

    Kaper

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Count of Unique Values Based on Date in Another Column

    With your dates in C2, C3, etc, here's an alternative array formula - enter in D2 (with Ctrl-Shift-Enter) and drag down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Obviously amend the ranges A$2:A$13 / B$2:B$13 to your full range (A$2:A$1000 or whatever) .

    Kaper's using COUNTIFS is probably better, but I'm stuck with 2003 at work just now, so can't use COUNTIFS - the formula above will work in 2003 as well.
    If you like it, please consider giving some rep to Tony Valko as it's adapted from his formula here: http://www.excelforum.com/excel-form...ml#post3308120
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count of Unique Values Based on Date in Another Column

    Try this...

    Data Range
    A
    B
    C
    D
    E
    1
    2
    3/6/2014
    308011
    3/6/2014
    3
    3
    3/6/2014
    308011
    3/7/2014
    2
    4
    3/6/2014
    308035
    3/8/2014
    3
    5
    3/6/2014
    308047
    ------
    ------
    ------
    6
    3/7/2014
    308011
    7
    3/7/2014
    308035
    8
    3/7/2014
    308035
    9
    3/7/2014
    308035
    10
    3/8/2014
    308011
    11
    3/8/2014
    308035
    12
    3/8/2014
    308047
    13
    3/8/2014
    308047


    This array formula** entered in E2 and copied down:

    =SUM(IF(FREQUENCY(IF(A$2:A$13=D2,B$2:B$13),B$2:B$13),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count of Unique Values Based on Date in Another Column

    Quote Originally Posted by Aardigspook View Post
    =SUM(IF(FREQUENCY(IF(A$2:A$13=C8,MATCH(B$2:B$13,B$2:B$13,0)),ROW(B$2:B$13)-ROW(B2)+1),1))
    When the data being counted is numeric (count of unique numbers) you can replace the MATCH and ROW functions with just the data range. See my example in post #5.

    using COUNTIFS is probably better
    Actually, the SUM(IF(FREQUENCY version is more efficient especially on large ranges of data.

  7. #7
    Registered User
    Join Date
    02-08-2013
    Location
    toronto, ON
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Count of Unique Values Based on Date in Another Column

    Thank you everyone for such a prompt solution. Kaper I used your formula and it works perfectly but I have one problem that it has slowed down whole worksheet which has a lot of data including the data that I'm using for this calculation around 300k rows. Is there any way I can work around on speed? or may be we can use some macros to do the same calculations? Just to explain it little further, Data is in one sheet and i'm using formula in another sheet.

    Also I forgot to mention there is another column right next to it which says "Type" and values are either 'new' or existing'. In step 2, I need to count the same unique values but based on either if Column C has 'New' or 'Existing' separately. Please note, I can't use pivot table for this.

    Thanks again everyone.

  8. #8
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Count of Unique Values Based on Date in Another Column

    Quote Originally Posted by masood78 View Post
    Is there any way I can work around on speed?
    Try Tony's array formula instead:
    =SUM(IF(FREQUENCY(IF(A$2:A$13=D2,B$2:B$13),B$2:B$13),1))

    Quote Originally Posted by Tony Valko View Post
    the SUM(IF(FREQUENCY version is more efficient especially on large ranges of data.

+ 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. Replies: 2
    Last Post: 08-19-2015, 10:22 AM
  2. [SOLVED] Count Unique Values based on Column
    By raw_geek in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-15-2014, 01:55 PM
  3. Count Unique values in Column based on values in other columns
    By dmschave in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2014, 11:06 AM
  4. [SOLVED] count unique values across multiple sheets based on date
    By roninn75 in forum Excel General
    Replies: 8
    Last Post: 02-25-2014, 05:20 PM
  5. count unique values in columns based on date values
    By cclntn in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2010, 06:16 PM
  6. Count Unique Values based on another column
    By Ineedhelp12 in forum Excel General
    Replies: 2
    Last Post: 07-02-2009, 06:12 PM
  7. Count unique values based on another column
    By verdugan in forum Excel General
    Replies: 4
    Last Post: 07-02-2009, 12:21 PM

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