Counting multiple customer sales over 3 month period

[GillyUK]

My company is concerned about customer retention and we need to identify those customers who have purchased at any point within a 3 month window i.e. the current month and the preceding 2 months and then show them as a count for each month. Logically, if the sales team are doing their job, this count should go up each month. We have decided this is the best way to tackle the problem rather than count the zeros over the 3 month windows.

I have attached a sample spreadsheet - for Jan.13, the last month, I have shaded the cells that would be contained within the count for that month and physically put the count total at the bottom (34). I have also manually counted the results for Sep.12, Oct.12, Nov.12, & Dec.12, merely to show the results to be expected. Clearly there would be no calculations for the first 2 months Nov.11 & Dec.11.

Thank you
Gillycustomer retention sample.xlsx

[bebo021999]

Try this for last month:
=SUMPRODUCT(--(N$2:N$41+O$2:O$41+P$2:P$41>0))
Copy to columns in the left

[GillyUK]

That works a treat. Thank you very much.

[Sean Thomas]

You could also try this
with the active cell being the cell below the current month column (where you want the tootal)
run this macro

Please Login or Register  to view this content.