Count of number of unique customers by month

user_11375181 0
Viewed 1163

I have a spreadsheet that contains data for customers that have purchased items from my store. What I would like to do is go back the last couple years, using a pivot table, and display: number of customers that ordered something each month (a count of unique customer names).

The sheet is organized as such:

Date     | Invoice | Account Name     | Item            | Quantity | Amount
6/1/2020 | 50100   | John's Tire Shop | 1/2 Inch Socket | 2        | 12.00

Is there a way inside pivot table options to count unique account names and display the total grouped by month?

pivot-table excel-formula excel
2 Answers

You can create a helper column to identify unique values per month to add as a filter to your pivot using your existing setup

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,"y","n")

enter image description here

user_67064190

I would propose you to add a helper column to your data, containing the =Month(A:A) function. But be careful: I see that your first date equals "6/1/2020", is that the first of June or is this Epiphany? :-)

user_42791550
Related