the_Apple

asked on

# 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

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

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"

=SUM(IF(FREQUENCY(IF(TEXT(

where cell D2 should contain a month text in the form of "JUL11"

ASKER CERTIFIED SOLUTION

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

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$262

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

http://office.microsoft.com/en-us/excel-help/count-occurrences-of-values-or-unique-values-in-a-data-range-HP003056118.aspx