Distinct values with multiple criteria

[john-paul]

Hi,

I need a formula that can count distinct number customers (ie: count each customer only once), for each combination of Division and Order Value Tier.

So for the combination of:
Division = East
Order Value Tier = 0 - 100k
Result = 3
So it counts ACME once, ABC once and XYZ.


Thanks John

Customer	Division	Order Value Tier
ACME	East	0 - 100k
ACME	East	0 - 100k
ABC	East	0 - 100k
ABC	East	0 - 100k
XYZ	East	0 - 100k
ACME	East	100k - 500k
ACME	East	100k - 500k
ABC	East	100k - 500k
XYZ	East	100k - 1M
ACME	West	0 - 100k
ABC	West	100k - 500k
XYZ	West	100k - 500k

 

[maabadi]

• For more Clarification, please upload example with XL2BB add-in (Preferable) OR upload at free hosting site e.g. GoogleDrive, OneDrive, Dropbox and Insert Link at post.
• Please update your Account details, so we know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[hrayani]

You need to update your account details
see if this helps,

Book2
	A	B	C
1	Customer	Division	Order Value Tier
2	ACME	East	0 - 100k
3	ACME	East	0 - 100k
4	ABC	East	0 - 100k
5	ABC	East	0 - 100k
6	XYZ	East	0 - 100k
7	ACME	East	100k - 500k
8	ACME	East	100k - 500k
9	ABC	East	100k - 500k
10	XYZ	East	100k - 1M
11	ACME	West	0 - 100k
12	ABC	West	100k - 500k
13	XYZ	West	100k - 500k
14
15
16	Customer	West	Order Value Tier
17	ACME	West	0 - 100k
18	ABC	West	100k - 500k
19	XYZ	West	100k - 500k
Sheet1

Cell Formulas
Range	Formula
A17:A19	A17	=IFERROR(INDEX($A$2:$A$13, MATCH(0, IF($B$17=$B$2:$B$13, COUNTIF($A$16:$A16, $A$2:$A$13), ""), 0)),"")
B17:B19	B17	=$B$16
C17:C19	C17	=IFERROR(INDEX($C$2:$C$13,MATCH(1,(A17=$A$2:$A$13)*(B17=$B$2:$B$13),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[maabadi]

Try this With CTRL+SHIFT+ENTER:

Book1
	A	B	C	D	E	F	G	H	I
1	Customer	Division	Order Value Tier			Division	Order Value Tier	Countif
2	ACME	East	0 - 100k			East	0 - 100k	3
3	ACME	East	0 - 100k			East	100k - 500k	2
4	ABC	East	0 - 100k			West	0 - 100k	1
5	ABC	East	0 - 100k			West	100k - 500k	2
6	XYZ	East	0 - 100k
7	ACME	East	100k - 500k
8	ACME	East	100k - 500k
9	ABC	East	100k - 500k
10	XYZ	East	100k - 1M
11	ACME	West	0 - 100k
12	ABC	West	100k - 500k
13	XYZ	West	100k - 500k
14
15
16
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=SUM(IF((G2=$C$2:$C$13)*($B$2:$B$13=F2), 1/COUNTIFS($A$2:$A$13, $A$2:$A$13, $B$2:$B$13, $B$2:$B$13, $C$2:$C$13,$C$2:$C$13), 0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kvsrinivasamurthy]

D4	E4	F4
East	0 - 100k	3

Data in A2:C13
ARRAY formula in F4, to be confirmed with Ctrl+Shift+Enter keys together.

=SUM(IFERROR(1/(MMULT(1*(IF(($C$2:$C$13=E4)*($B$2:$B$13=D4),$A$2:$A$13,"")=TRANSPOSE(IF(($C$2:$C$13=E4)*($B$2:$B$13=D4),$A$2:$A$13,"X"))),1*(ROW($A$2:$A$13)>0))),0))

 

[john-paul]

Thank you all for your help.

Solutions by maabadi and kvsrinivasamurthy both worked - really appreciate the help.

 

[maabadi]

You're Welcome & Thanks for follow-up.