Counting as one when one unique customer purchase item with multiple time in a month

[blader1989]

Hi all,

Currently I'm working with a report that show the metric ECO in FMCG industry (ECO- Effectively Covered Outlet - just count if the total value large than 0) but I'm now stuck in how to count as one when one unique customer purchase multiple time in a month. I'm using the countifs function but it just only counts the item in multiple time as you can see in mini sheet below, my expectation it should count as 1 (formula in E11 row).

Any help would be appreciated. Thank you for reading my post!!!

eco.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
2	Product Name	Customer Code	Province	Year	Month	CAT	REGION2	Total (-VAT)	Quarter	Sub-Channel	Sub-Brand	Month-Year
3	Product A	121090300	HCMCT	2020	1	OH	South	560000	Q1	GENERAL CLINIC	SSD	Jan-20
4	Product A	121090300	HCMCT	2020	1	OH	South	2880000	Q1	GENERAL CLINIC	SSD	Jan-20
5	Product A	121090300	HCMCT	2020	1	OH	South	5760000	Q1	GENERAL CLINIC	SSD	Jan-20
6
7
8
9
10	Metric	Product Name	Channel	Area	Jan-20
11	ECO	Product A	GENERAL CLINIC	South	3	It should be as 1 since this product was purchased by one unique customer in multiple time of Jan 20
raw

Cell Formulas
Range	Formula
E11	E11	=COUNTIFS(raw!$H$2:$H$5,">0",raw!$A$2:$A$5,B11,raw!$J$2:$J$5,C11,raw!$G$2:$G$5,D11,raw!$L$2:$L$5,E10)

 

[Eric W]

Try:

Spark.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Product Name	Customer Code	Province	Year	Month	CAT	REGION2	Total (-VAT)	Quarter	Sub-Channel	Sub-Brand	Month-Year
3	Product A	121090300	HCMCT	2020	1	OH	South	560000	Q1	GENERAL CLINIC	SSD	Jan-20
4	Product A	121090300	HCMCT	2020	1	OH	South	2880000	Q1	GENERAL CLINIC	SSD	Jan-20
5	Product A	121090300	HCMCT	2020	1	OH	South	5760000	Q1	GENERAL CLINIC	SSD	Jan-20
6
7
8
9
10	Metric	Product Name	Channel	Area	Jan-20
11	ECO	Product A	GENERAL CLINIC	South	1	It should be as 1 since this product was purchased by one unique customer in multiple time of Jan 20
raw

Cell Formulas
Range	Formula
E11	E11	=ROWS(UNIQUE(FILTER(raw!B2:B5,(raw!$H$2:$H$5>0)*(raw!$A$2:$A$5=B11)*(raw!$J$2:$J$5=C11)*(raw!$G$2:$G$5=D11)*(raw!$L$2:$L$5=E10))))