Finding Distinct Values with a Formula

[oz74]

Hi

I need a formula which will count how many distinct values I have in a particular field in my dataset – I envisage something similar to COUNTIFS / SUMIFS. Below is a screenshot of my data:

The formula needs to count the number of distinct Customer IDs in a country but have the ability to add other conditions. For example, determining the number of distinct Customer IDs in Brazil who are Corporate Customers. I envisage this to be a stand-alone formula in a cell rather producing a list of countries. Below is a visualisation which may help:

I’ve also pasted the data as text to copy into Excel:

Country Customer ID Customer Type Sales ID
Argentina A515 Corporate B22598
Australia M877 Retail H98748
Bangladesh Y874 Retail K85974
Argentina A515 Corporate B58748
Belgium D874 Corporate Z87489
Australia S597 Retail H48712
Brazil T282 Corporate Q19123
Australia S597 Retail J98194
Austria F487 Corporate Q87458
Bahrain W874 Corporate U89859
Argentina A522 Retail C59845
Belgium D874 Corporate R88872
Belgium R784 Retail R11165
Brazil F456 Corporate D90822
Canada J765 Retail S90232
Chile A453 Retail A98123
Brazil F456 Corporate P00983
Brazil F456 Corporate S87321
Brazil K987 Corporate D90921
Chile P890 Retail A09123
Chile P890 Retail H34098


Thank you in advance for your help, much appreciated.

 

[Fluff]

Can you do a straight copy/paste from excel to here (ie Ctrl C then Ctrl v) that way we get usable data like

PostCode	In Use?	Latitude	Longitude	Easting	NorTHI	GridRef
BN1 7GB	Yes	50.841392​	-0.130888​	531697​	106316​	TQ316063
CB25 0DU	Yes	52.277825​	0.326272​	558800​	266957​	TL588669
LS13 3DJ	Yes	53.809715​	-1.639067​	423865​	434885​	TL588670
SO51 7SA	Yes	50.994629​	-1.49191​	435754​	121815​	SU357218
ST16 3QU	Yes	52.813977​	-2.093934​	393766​	324052​	SJ937240
E5 0QG	Yes	51.558042​	-0.054134​	534991​	186145​	TQ349861
SG13 7FZ	Yes	51.800314​	-0.072611​	532999​	213053​	TL329130
SN2 1BD	Yes	51.569009​	-1.781816​	415219​	185593​	SU152855
TW3 1NN	Yes	51.470857​	-0.361684​	513889​	175927​	TQ138759

Rather than everything mixed in together like yours is

 

[oz74]

Hi Fluff, Safari doesn't paste as a table of data but Chrome does so here it is:

Country	Customer ID	Customer Type	Sales ID
Argentina	A515	Corporate	B22598
Australia	M877	Retail	H98748
Bangladesh	Y874	Retail	K85974
Argentina	A515	Corporate	B58748
Belgium	D874	Corporate	Z87489
Australia	S597	Retail	H48712
Brazil	T282	Corporate	Q19123
Australia	S597	Retail	J98194
Austria	F487	Corporate	Q87458
Bahrain	W874	Corporate	U89859
Argentina	A522	Retail	C59845
Belgium	D874	Corporate	R88872
Belgium	R784	Retail	R11165
Brazil	F456	Corporate	D90822
Canada	J765	Retail	S90232
Chile	A453	Retail	A98123
Brazil	F456	Corporate	P00983
Brazil	F456	Corporate	S87321
Brazil	K987	Corporate	D90921
Chile	P890	Retail	A09123
Chile	P890	Retail	H34098

 

[Fluff]

Thanks for that
How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Country	Customer ID	Customer Type	Sales ID
2	Argentina	A515	Corporate	B22598		Brazil	2
3	Australia	M877	Retail	H98748		Corporate
4	Bangladesh	Y874	Retail	K85974
5	Argentina	A515	Corporate	B58748		Brazil	3
6	Belgium	D874	Corporate	Z87489
7	Australia	S597	Retail	H48712
8	Brazil	T282	Corporate	Q19123
9	Australia	S597	Retail	J98194
10	Austria	F487	Corporate	Q87458
11	Bahrain	W874	Corporate	U89859
12	Argentina	A522	Retail	C59845
13	Belgium	D874	Corporate	R88872
14	Belgium	R784	Retail	R11165
15	Brazil	F456	Corporate	D90822
16	Canada	J765	Retail	S90232
17	Chile	A453	Retail	A98123
18	Brazil	F456	Corporate	P00983
19	Brazil	F456	Corporate	S87321
20	Brazil	K987	Retail	D90921
21	Chile	P890	Retail	A09123
22	Chile	P890	Retail	H34098
23
Details

Cell Formulas
Range	Formula
G2	G2	=ROWS(UNIQUE(FILTER(B2:B22,(A2:A22=F2)*(IF(F3="",1,C2:C22=F3)))))
G5	G5	=ROWS(UNIQUE(FILTER(B2:B22,(A2:A22=F5)*(IF(F6="",1,C2:C22=F6)))))

 

[oz74]

Thanks for that
How about

Thank you very much Fluff - it works brilliantly. To share - I've also added another condition to your formula:

=ROWS(UNIQUE(FILTER(B2:B22,(A2:A22=G5)*(IF(G6="",1,C2:C22=G6))*(IF(G7="",1,E2:E22=G7)))))

You've been incredibly helpful!

 

[Fluff]

You're welcome & thanks for the feedback.