+ Reply to Thread
Results 1 to 8 of 8

Formula to Count Unique Customer by Month

  1. #1
    Registered User
    Join Date
    08-13-2021
    Location
    Brasil
    MS-Off Ver
    2016
    Posts
    4

    Question Formula to Count Unique Customer by Month

    Hello,

    I am trying to get to a new client count by month. So I have two columns (Customer ID and Customer Start Date) with duplicate records. Thank you so much in advance!

    Data Provided
    Customer ID (Column A)
    1) 23
    2) 24
    3) 25
    4) 24
    5) 28
    6) 28
    7) 30

    Month (Column B)
    1) 11/1/2009
    2) 11/1/2009
    3) 12/1/2009
    4) 13/1/2009
    5) 14/1/2009
    6) 12/2/2009
    7) 13/2/2009

    Answers
    - 4 clients in JAN (customer 23, 24, 25 and 28)
    - 2 clients in FEB (customer 28 and 30)

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Formula to Count Unique Customer by Month

    I suggest you use a pivot table. It will do this automatically without needing to write a formula. If you provide an actual Excel file I can show you how to set it up. See yellow banner at the top of the page.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    08-13-2021
    Location
    Brasil
    MS-Off Ver
    2016
    Posts
    4

    Re: Formula to Count Unique Customer by Month

    Thanks for the help, I tried pivot table but it doesn't suit me, at least I couldn't do it.
    Follow prints.



    Attachment 744162

    Attachment 744165
    Attached Files Attached Files
    Last edited by hokttor; 08-15-2021 at 09:27 AM.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Formula to Count Unique Customer by Month

    You need to have all your data in two columns, not four.

    Looking at your data I am not sure what it means. Your first post said "start date" but how can one client have more than one start date?

    In your file, your chart shows 4 for March, but your data lists 5 clients with start dates in March. Your chart shows 8 for April but the data only has 6. And so on. How are you getting the numbers for your chart?

  5. #5
    Registered User
    Join Date
    08-13-2021
    Location
    Brasil
    MS-Off Ver
    2016
    Posts
    4

    Re: Formula to Count Unique Customer by Month

    Thank you for your help.

    Regarding the columns in the original spreadsheet, there are only two.

    I expressed myself poorly when I said start date, I was referring to customer purchases in the months.

    About the graphic, it's just an expectation of how I wanted it to be. Taking the example you mentioned:
    March
    In March we had 5 customers, but 1 of them is repeated, so we had 4 customers in March, as a customer bought twice in the same month of March, but on different days.

    03/15/2021 25.....> 1st purchase
    03/15/2021 22
    25/03/2021 25.....> 2nd purchase
    03/31/2021 31
    03/31/2021 32

    Note: I improved the spreadsheet, my question may have been confused.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    03-05-2018
    Location
    Tbilisi, Georgia
    MS-Off Ver
    2013, 2016
    Posts
    271

    Re: Formula to Count Unique Customer by Month

    Hello, Try this:
    =SUM(IF(FREQUENCY(IF(($A$6:$A$41>=H$1)*($A$6:$A$41<=EOMONTH(H$1,0)),MATCH($C$6:$C$41,$C$6:$C$41,0)),ROW($C$6:$C$41)-ROW($C$6)+1),1))
    Confirmed with CTRL+SHIFT+ENTER
    Months in text in the first row replaced with the dates.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-13-2021
    Location
    Brasil
    MS-Off Ver
    2016
    Posts
    4

    Re: Formula to Count Unique Customer by Month

    Thank you so much, you saved us, we would have to do everything manually one by one.
    Again, thank you very much.

  8. #8
    Forum Contributor
    Join Date
    03-05-2018
    Location
    Tbilisi, Georgia
    MS-Off Ver
    2013, 2016
    Posts
    271

    Re: Formula to Count Unique Customer by Month

    You're welcome. Glad to help

+ 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 per month
    By tosteven in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-26-2022, 11:28 AM
  2. [SOLVED] Count a customer if they appear for sequential periods (by month), fresh count if not
    By AnnieMM in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2021, 08:35 AM
  3. [SOLVED] display unique customer formula
    By macrorookie in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-24-2020, 01:01 AM
  4. Count new customer per month
    By Rexn59 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-26-2019, 01:39 PM
  5. Replies: 2
    Last Post: 11-21-2014, 12:59 PM
  6. [SOLVED] Formula to count unique strings broken down by month
    By Cabs1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-27-2014, 10:34 AM
  7. [SOLVED] Formula to Count Unique Customer by Month
    By byankton in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-19-2013, 12:39 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