Count Unique Customers per Year

[maria90]

I've got the task to find out the repeat customer rate per year:
Customers that have purchased more than once / unique customers

I have no problems finding out the number of unique customers; I've got a customer table which counts the date of the customer's first order.

I do, however, not know how to count those customers who have made more than one order per year.
I thought that SUMPRODUCT would be the ideal formula to get the result with a COUNTIF in it in order to get those customers who have made more than one order It doesn't work as I get an astronomically high number.

I've got a table called Orders - with [Date] and [CustomerID] amongst other data recorded.

This is my formula

=SUMPRODUCT((YEAR(Orders[Date])=2011)*(COUNTIF(Orders[CustomerID];">1")))

Thanks for your help

Maria

 

[proficient]

Only want to count those customer who purchased more than one in a year or want to extract their names too...?

 

[maria90]

No, just the number of customers who have made more than one order, for example in the year 2011.

 

[gaz_chops]

Maybe

=SUMPRODUCT((YEAR(Orders[Date])=2011)*(Orders[CustomerID]>1))

 

[Comfy]

One option would be to pivot the data.

Row = Customer
Column = Year
Value = Count of Customer

Then use a summary table to count those greater than 1.

Excel 2010

	A	B	C	D	E	F
Order Per Year	Years
Customers
A
B
C
D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2015[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: center"]Repeat Customers[/TD]
[TD="bgcolor: #DCE6F1, align: center"][/TD]
[TD="bgcolor: #DCE6F1, align: center"][/TD]
[TD="bgcolor: #DCE6F1, align: center"][/TD]
[TD="bgcolor: #DCE6F1, align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"]2011[/TD]
[TD="bgcolor: #DCE6F1, align: right"]2012[/TD]
[TD="bgcolor: #DCE6F1, align: right"]2013[/TD]
[TD="bgcolor: #DCE6F1, align: right"]2014[/TD]
[TD="bgcolor: #DCE6F1, align: right"]2015[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]=COUNTIF(B5:B8,">1")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Excel 2010
	A	B
1	Customer	Year
2	A	2011
3	A	2011
4	A	2011
5	A	2012
6	B	2012
7	B	2013
8	B	2014
9	C	2012
10	C	2012
11	C	2015
12	D	2015
Source Data

 

[maria90]

A pivot table seems to be an ideal solution. I gave it a try and the value returned doesn't seem to be right.
I have attached the workbook to this file. Download here.

The pivot table is supposed to count the number of customers per year. But that doesn't seem right because we don't have that many customers. Around 1300 in total.

Anyway, I've attached the workbook and I am still wondering whether it's possible to retrieve the data with a formula - when necessary with hidden cells.
Gaz's formula doesn't work.

Thanks for your help
Maria

 

[AhoyNC]

See if this works.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Below are the answers I got for years 2011 thru 2013.The ROW([Orderes[@CustomerID) refers to row C2

Excel Workbook
	N	O
1	Year	Customer >1 order
2	2011	116
3	2012	268
4	2013	409
Sheet

 

[Aladin Akyurek]

@maria90
@AhoyNC

Since the customer ids appear to be numbers, the following would run faster...

F1, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(YEAR(Orderes[Date])=E1,Orderes[CustomerID]),Orderes[CustomerID])>1,1))

where E1 houses a year of interest like 2011.

 

[SpillerBD]

It actually will take two Pivot Tables.
See my modifications to your sample WorkBook http://1drv.ms/1M4hxhW

In the PT you imaged, the excessive count is from the number of transactions....