Getting the number of customer in year 2011

[ShirLowMingHan]

Hey guys! I am looking a way out to get the number of unique customer that have bought product in year 2011. And the table contains full date from 2011 to 2013. I have been using =SUM(COUNTIFS(Table1[Date],">="&DATE(2011,1,1),Table1[Date],"<="&DATE(2011,12,31))) to get the number of rows that date between 2011 and =COUNTA(UNIQUE(Table1[Customer Name])) to get the number of unique customer. But when I tried to combine the I failed to get the correct outcome... Appreciate your help, also if there is any method that is easier than mine please do let me know!

 

[Alex Blakenburg]

See if one of these works for you.

Book1
	A	B	C	D	E	F
1	Unique Cust	7		Unique Cust	7
2	Year	2011		From	1/01/2011
3				To	31/12/2011
28
29
30		Date	ProductID	Cust ID	Quantity	Product Name
31		1/01/2011		Cust00001
32		1/01/2011		Cust00057
33		1/01/2011		Cust00015
34		1/01/2011		Cust00035
35		1/01/2011		Cust00031
36		1/01/2011		Cust00017
37		1/01/2011		Cust00046
38		1/01/2011		Cust00001
39		1/01/2011		Cust00057
40		1/01/2011		Cust00015
41		1/01/2011		Cust00035
42		1/01/2012		Cust00001
43		1/01/2012		Cust00057
44		1/01/2012		Cust00015
45		1/01/2012		Cust00035
46		1/01/2012		Cust00031
47		1/01/2012		Cust00017
48		1/01/2012		Cust00046
49		1/01/2012		Cust00035
50		1/01/2012		Cust00031
51		1/01/2012		Cust00017
52		1/01/2012		Cust00046
Data

Cell Formulas
Range	Formula
B1	B1	=COUNTA(UNIQUE(FILTER(Table1[Cust ID],YEAR(Table1[Date])=$B$2)))
E1	E1	=COUNTA(UNIQUE(FILTER(Table1[Cust ID],(Table1[Date]>=$E$2)*(Table1[Date]<=$E$3))))