+ Reply to Thread
Results 1 to 12 of 12

Unique Count (Subscribers) based on Date

  1. #1
    Registered User
    Join Date
    06-20-2014
    Location
    Cuddalore, Tamilnadu, India
    MS-Off Ver
    MS Office 2013
    Posts
    22

    Unique Count (Subscribers) based on Date

    Hi Concern,

    Please help me to get unique customer id with Date and Amount.

    For instance, Total 45376 records in the sheet. I have got the below first 3 columns from Pivot.

    Now i need Unique Numbers column based on Amount and Date.

    tried manually calculated from Pivot unique CustomerID for 15th Nov with 90 Amount is 4844 same as 135 amount will follow 523 unique numbers.

    How to find the easiest way to calculate all in one shot.


    Date Amount Total_Nos Unique_Numbers
    15/11/2018 90 6,668.00 4,844.00
    15/11/2018 135 889.00 523.00
    15/11/2018 225 13,969.00 ?
    15/11/2018 450 3,450.00 ?
    15/11/2018 900 35.00
    15/11/2018 2250 3.00
    16/11/2018 90 5,197.00
    16/11/2018 135 638.00
    16/11/2018 225 11,825.00
    16/11/2018 450 2,653.00
    16/11/2018 900 42.00
    16/11/2018 2250 7.00



    Attached sheet for your reference

    Thanks,
    Siva
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Unique Count (Subscribers) based on Date

    Please check out here:
    https://stackoverflow.com/questions/...distinct-count
    Regards,
    Thangavel D

    Appreciate the help? CLICK *

  3. #3
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Unique Count (Subscribers) based on Date

    In I2 :
    use this formual
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This is a CSE formula (Enter formula with CTRL+SHIFT+ENTER)
    Attached Files Attached Files
    Last edited by than_gold; 11-21-2018 at 09:21 AM. Reason: attaching file

  4. #4
    Registered User
    Join Date
    06-20-2014
    Location
    Cuddalore, Tamilnadu, India
    MS-Off Ver
    MS Office 2013
    Posts
    22

    Re: Unique Count (Subscribers) based on Date

    =SUM(IF(FREQUENCY(IF($C$2:$C$45377=G2,IF($A$2:$A$45377=F2,$B$2:$B$45377)),$B$2:$B$45377)>0,1))

    its giving #value

  5. #5
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Unique Count (Subscribers) based on Date

    This is a CSE formula (Enter formula with CTRL+SHIFT+ENTER)
    After you enter the formula press C+S+E together & you can see {} to close the formual
    Have you checked the attached file?

  6. #6
    Registered User
    Join Date
    06-20-2014
    Location
    Cuddalore, Tamilnadu, India
    MS-Off Ver
    MS Office 2013
    Posts
    22

    Re: Unique Count (Subscribers) based on Date

    Still getting same.. I do not know whether i am getting rigtht answer here

  7. #7
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Unique Count (Subscribers) based on Date

    Please check the screenshot,
    in J2 I used your formula & you notice {} in the formula. To get that you need to press Ctrl+Shift+Enter
    j2 with CSE.PNG

    Check here for CSE formula: https://www.ablebits.com/office-addi...nctions-excel/

  8. #8
    Registered User
    Join Date
    06-20-2014
    Location
    Cuddalore, Tamilnadu, India
    MS-Off Ver
    MS Office 2013
    Posts
    22

    Re: Unique Count (Subscribers) based on Date

    Thanks, let me try

  9. #9
    Registered User
    Join Date
    06-20-2014
    Location
    Cuddalore, Tamilnadu, India
    MS-Off Ver
    MS Office 2013
    Posts
    22

    Re: Unique Count (Subscribers) based on Date

    You save me lot..one thing i noticed CTRL+Shift+Enter its coming all the way first value to all, why this not changing cell by cell..
    I did it one by one "C+S+E

  10. #10
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Unique Count (Subscribers) based on Date

    no need to do by cell by cell.
    You just do for first & dopy+paste or ctdl+D for below cells.

  11. #11
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Unique Count (Subscribers) based on Date

    Add rep if you like my assistance...

  12. #12
    Registered User
    Join Date
    06-20-2014
    Location
    Cuddalore, Tamilnadu, India
    MS-Off Ver
    MS Office 2013
    Posts
    22

    Re: Unique Count (Subscribers) based on Date

    Thank you

+ 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] Count unique values based on value with date range
    By papasmurfuo9 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-24-2018, 07:24 AM
  2. Count of Unique Values Based on Date in Another Column
    By masood78 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-13-2015, 03:46 AM
  3. [SOLVED] Count Unique Values Based on Earliest Date Criteria
    By Aquamore in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-04-2014, 09:12 PM
  4. [SOLVED] Count Unique Instances of Date while filtering based upon date and ID #
    By pmambrosetti in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-15-2014, 02:22 PM
  5. Count Unique based on date
    By dab1988 in forum Excel General
    Replies: 14
    Last Post: 08-25-2011, 08:17 AM
  6. Sort,Count & Sum data based on unique date
    By excelkeechak in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-26-2009, 06:57 AM
  7. count unique cells in range based on date
    By leedsd75 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-09-2007, 02:04 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