Count Unique Coustomers in excel by Month

I have a table with the following fields

CoustomerID   Date  


one coustomer may order 50 things in one month. I want to be able to count the number of coustomer ordering per month.

Any help?
sample.xlsx
the_AppleAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Edit: I missed Saqib's suggestion, posted while I was composing this - my suggestion is very similar.....

If you put the first of each month in F2 down then this "array formula" in G2 will give a "distinct count" of customers for that month

=SUM(IF(FREQUENCY(IF(B$2:B$3000-DAY(B$2:B$3000)+1=F2,MATCH(A$2:A$3000,A$2:A$3000,0)),ROW(B$2:B$3000)-ROW(B$2)+1),1))

confirmed with CTRL+SHIFT+ENTER and copied down

see attached

Note: you have some dates that I assume are incorrect, e.g. one in 2020, one in 2070

regards, barry
distinct-count.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
Try this formula

=SUM(IF(FREQUENCY(IF(TEXT(B2:B2628,"MMMYY")=$D$2,MATCH(A2:A2628,A2:A2628,0),""), IF(LEN(A2:A2628)>0,MATCH(A2:A2628,A2:A2628,0),""))>0,1))

where cell D2 should contain a month text in the form of "JUL11"
0
 
Patrick MatthewsCommented:
To get a count of distinct customers by month:

1) In C1 of your original worksheet, add a label such as Unique Customers

2) In C2, add this formula...
=1/SUMPRODUCT(($A$2:$A$2628=A2)*(TEXT($B$2:$B$2628,"yyyy-mm")=TEXT(B2,"yyyy-mm")))
then copy that down through C2628

3) Create a PivotTable, with ApptDate as the row field, and (sum of) Unique Customers as the data field

4) Rightclick one of the dates in your PivotTable, and select Group from the popup menu.  Group your dates by year and month

Please see the attached sample file.

Q-27980493.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.