# 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
schaps

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"
barry houdini

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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