Count of unique customers by month

1. 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.

2. Re: Count of unique customers by month

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

3. Re: Count of unique customers by month

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

4. Re: Count of unique customers by month

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. 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.

6. 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. Re: Count of unique customers by month

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

8. 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. 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. 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. Re: Count of unique customers by month

OK Thank you.

12. Re: Count of unique customers by month

Let us know how you get on.

There are currently 1 users browsing this thread. (0 members and 1 guests)