+ Reply to Thread
Results 1 to 12 of 12

Count of unique customers by month

  1. #1
    Registered User
    Join Date
    08-28-2022
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    7

    Post Count of unique customers by month

    I have a dataset which contains emailid, creation date, and quantity. I want to find out how many new emails got created in a particular month. Ex: 10 emails got created in January and in February 5 emails got created from which 2 emails are from January so in February 3 new emails got created. I need a dynamic functions if data changes the function also updates the data. I also need the count of repeated emails for each month. Taking the example from above 5 emails got created in February out of which 2 are same email from January so repeated email count will be 2.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Count of unique customers by month

    Which version of Excel do you have? Is this some sort of homework?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    08-28-2022
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    7

    Re: Count of unique customers by month

    I have 2021 version. Yes, It is a homework.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Count of unique customers by month

    Please udpate your forum profile.

    So what have you tried so far? I don't see any attempts to solve the problem in the workbook attached.

    Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

    We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

    If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.

  5. #5
    Registered User
    Join Date
    08-28-2022
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    7

    Post Re: Count of unique customers by month

    This is the copy of the file on which I have been working on. I will share the file in which I have worked. The attached file also has the same data. In Report_Summary_Copy sheet I tried solving the question but I think the
    answer which I am getting is not correct. I tried to find the unique values i.e new email using unique function and I got 2 different result when I change the value
    of the function. UNIQUE(array,[by Col],[exactly once]) when I change [exactly once] to FALSE I get different result and when it is TRUE I get different result.
    I am not sure which one to take. I have used sumproduct and 1/countif to get the output.

    Thank you for responding to my thread.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Count of unique customers by month

    If you have the UNIQUE function, then you don't have Excel 2016 - what do you have? Please update your forum profile.

  7. #7
    Registered User
    Join Date
    08-28-2022
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    7

    Re: Count of unique customers by month

    I am using MS-Office 2021 version. I have changed in my forum profile.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Count of unique customers by month

    Thanks. When you say that you don't 'think' that the answers are correct, on what basis do you say this?

    When creating formulae, you need to test them by manually calculating the correct answer for some of the cells, then checking that your formula tallies. Where have you put the results of your manual calculations? If you haven't done any, then now is the time to do some to test your theory.

    I am not sure which one to take.
    As I have just said, do some manual calculations to determine the answer to this.

  9. #9
    Registered User
    Join Date
    08-28-2022
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    7

    Re: Count of unique customers by month

    I need how many new email id is created. So I was confused whether to get distinct count of email id or unique count of email id. I checked on the internet and got the answer. I have to take the distinct count of email id. Now I am stuck with count of repeated email id. As per the data it starts from Aug-22 and goes till end of Jan-2023. In September new email id is created but the same email id is created in the month of October, Nov, Dec and Jan. I have to get the count of that and if you see the excel in Orders_Data sheet I created a new column "Repeated_Customer" to get the count of repeated emails. And for the same, I have created the Pivot Table i.e on "Sheet 3". But the count which I am getting in Pivot Table is very big. Which I am thinking is wrong. If you see "Report_Summary_Copy" I have filled the data but it doesn't seems to match as total number of rows in the dataset is 2442.
    Thank you.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Count of unique customers by month

    What you should do is take a manageable subset of the data that you can easily analyse manually by counting. Work out the correct reults manually and test your formulae on this. When you are satisfied that you are getting the right answers, THEN you can apply it to the full dataset.

  11. #11
    Registered User
    Join Date
    08-28-2022
    Location
    London, England
    MS-Off Ver
    2021
    Posts
    7

    Re: Count of unique customers by month

    OK Thank you.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Count of unique customers by month

    Let us know how you get on.

+ 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] Looking for formula to cumulatively count number of unique customers over time
    By walrus_22 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-25-2021, 03:03 AM
  2. Replies: 1
    Last Post: 10-19-2021, 11:05 PM
  3. Replies: 6
    Last Post: 09-08-2021, 08:57 AM
  4. [SOLVED] Retention formula to count unique customers that made a purchase in both years
    By stu40 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-23-2016, 01:24 AM
  5. Count of Age group with unique customers
    By Rushendra in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-23-2016, 01:21 AM
  6. count for unique customers
    By stephme55 in forum Excel General
    Replies: 9
    Last Post: 01-30-2016, 06:27 PM
  7. Unique Customers per month from table in graph form
    By faodavid in forum Excel General
    Replies: 6
    Last Post: 09-05-2015, 02:52 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