Count unique customer when buying multiple products

[blader1989]

Dear all,

I tried to find a solution that the formula will count the unique customer who buying multiple product is 1 as following the excel example below, the table below with column ECO is the formula I want to input. It is the ECO metric in FMCG industry. Thank you so much for reading my thread.

Sum of Total no VAT							Year
							2021​
Customer Code	REGION2	Sub-Channel	Channel	Province	Sub-Brand	Product Name	Jan
121080​	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	AQF	KDR Aquafresh Fresh & Minty 100ml	222
121080​	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	AQF	KDR AQUAFRESH TRE EM 6T 50ML - SAP	23456
121080​	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	AQF	KDR Aquafresh Triple Protection 100ml	1145
121080​	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	SSD	BCDR SENS SENSITIVE EXTRA SOFT 1X2	2356
121080​	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	SSD	KDR SENSODYNE BAC HA 100G	109045
121080​	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	SSD	KDR SENSODYNE RAPID ACTION 100G	373638​
121080​	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	SSD	KDR Sensodyne Repair & Proctect 100g	373638​
121080​	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	SSD	KDR Sensodyne Repair & Protect 100g
121080​	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	SSD	KDR SENSODYNE TRANG SANG 100G	9491854​

Customer	Jan-21​
121080​	ECO? Outcome expect is 1

 

[Sufiyan97]

Try

Book1
	A	B	C	D	E	F	G	H	I
1	Sum of Total no VAT							Year
2								2021
3	Customer Code	REGION2	Sub-Channel	Channel	Province	Sub-Brand	Product Name	Jan
4	121080	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	AQF	KDR Aquafresh Fresh & Minty 100ml	222
5	121080	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	AQF	KDR AQUAFRESH TRE EM 6T 50ML - SAP	23456
6	121080	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	AQF	KDR Aquafresh Triple Protection 100ml	1145
7	121080	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	SSD	BCDR SENS SENSITIVE EXTRA SOFT 1X2	2356
8	121080	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	SSD	KDR SENSODYNE BAC HA 100G	109045
9	121080	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	SSD	KDR SENSODYNE RAPID ACTION 100G	373638
10	121080	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	SSD	KDR Sensodyne Repair & Proctect 100g	373638
11	121080	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	SSD	KDR Sensodyne Repair & Protect 100g
12	121080	Central	GENERAL CLINIC	MEDICAL DEVICE SHOP	KhanhHoa	SSD	KDR SENSODYNE TRANG SANG 100G	9491854
13
14	Customer	Jan-21
15	121080	1
16
Sheet1

Cell Formulas
Range	Formula
B15	B15	=COUNTA(UNIQUE(FILTER($A$4:$A$12,$A$4:$A$12=A15)))

 

[blader1989]

Thank you so much. It solved my problem now ^^

 

[Fluff]

In future please mark the post that contains the solution, rather than your post saying it works. I have done that for you this time.
Thanks

 

[Sufiyan97]

Thank you so much. It solved my problem now ^^

You're welcome!

 

[Peter_SSs]

Thank you so much. It solved my problem now ^^

How so? That formula will always return 1 no matter what you put in A15 or A4:A12. Example

23 08 03.xlsm
	A	B
3	Customer Code	REGION2
4	a	Central
5	b	Central
6	c	Central
7	d	Central
8	e	Central
9	f	Central
10	g	Central
11	h	Central
12	i	Central
13
14	Customer	Jan-21
15	XXXXXXXXXX	1
Sample

Cell Formulas
Range	Formula
B15	B15	=COUNTA(UNIQUE(FILTER($A$4:$A$12,$A$4:$A$12=A15)))

Another

23 08 03.xlsm
	A	B
3	Customer Code	REGION2
4		Central
5		Central
6		Central
7		Central
8		Central
9		Central
10		Central
11		Central
12		Central
13
14	Customer	Jan-21
15		1
Sample

Cell Formulas
Range	Formula
B15	B15	=COUNTA(UNIQUE(FILTER($A$4:$A$12,$A$4:$A$12=A15)))