Count Unique Values Based On Criteria

[kumara_faith]

Hi,

I have the following:

Book1
	B	C	D	E	F	G	H	I	J	K
2	Sheet1						Sheet 4
3	Customer ID	Policy No	State	City	Zip Code		State	City	Zip Code	No of Customer
4	1000	77889745	Montgomery	Alabama	36104		Montgomery	Alabama	36104	6
5	1001	77889746	Montgomery	Alabama	36104		Texas	Dallas	35478	3
6	1002	77889747	Texas	Dallas	35478		Florida	Tallahassee	87895	6
7	1000	77889748	Montgomery	Alabama	36104		Missouri	Jefferson City	33658	6
8	1004	77889749	Florida	Tallahassee	87895
9	1005	77889750	Florida	Tallahassee	87895
10	1006	77889751	Missouri	Jefferson City	33658
11	1000	77889752	Montgomery	Alabama	36104
12	1008	77889753	Missouri	Jefferson City	33658
13
14
15	Sheet2
16	Customer ID	Policy No	State	City	Zip Code
17	1000	88784487	Montgomery	Alabama	36104
18	1001	88784488	Montgomery	Alabama	36104
19	1002	88784489	Texas	Dallas	35478
20	1000	88784490	Montgomery	Alabama	36104
21	1004	88784491	Florida	Tallahassee	87895
22	1005	88784492	Florida	Tallahassee	87895
23	1006	88784493	Missouri	Jefferson City	33658
24	1000	88784494	Montgomery	Alabama	36104
25	1008	88784495	Missouri	Jefferson City	33658
26
27
28	Sheet3
29	Customer ID	Policy No	State	City	Zip Code
30	1000	99874587	Montgomery	Alabama	36104
31	1001	99874588	Montgomery	Alabama	36104
32	1002	99874589	Texas	Dallas	35478
33	1000	99874590	Montgomery	Alabama	36104
34	1004	99874591	Florida	Tallahassee	87895
35	1005	99874592	Florida	Tallahassee	87895
36	1006	99874593	Missouri	Jefferson City	33658
37	1000	99874594	Montgomery	Alabama	36104
38	1008	99874595	Missouri	Jefferson City	33658
Sheet1

In Sheet 1 to Sheet3, I have the customer ID,, their policy no, state ,city and zip code. In Sheet4, i am trying to count the unique number of customers based on customer ID in each state and city. Example, for Montgomery, Alabama, based on unique customer ID, there is a total of 6 customers. I am unable to combine the list in Sheet 1 to 3 due to each sheet has a long list of customer details.

Is there a way to build a formula to look into sheet 1 to 3 and populate the result in Sheet4? Example of the correct result is in Sheet4. Appreciate all the help.

 

[Fluff]

How about

=LET(v,VSTACK(Sheet1:Sheet3!$A$2:$D$100),ROWS(UNIQUE(FILTER(TAKE(v,,1),(INDEX(v,,3)=G3)*(INDEX(v,,4)=H3)))))

 

[kumara_faith]

Hi Fluff,

Thank you for you solution. I dont have Vstack in the desktop version of my company but its available in the online version. But when I tried to open in Excel online, it says unable to open in Excel Online because it exceeds 100MB. Is there another alternative for the formula ?

 

[Fluff]

Ok, how about

=LET(a,Sheet1!$A$2:$D$100,b,Sheet2!$A$2:$D$100,c,Sheet3!$A$2:$D$100,ra,ROWS(a),rb,ra+ROWS(b),s,SEQUENCE(rb+ROWS(c)),x,IF(s<=ra,a,IF(s<=rb,INDEX(b,s-ra,{1,2,3,4}),INDEX(c,s-rb,{1,2,3,4}))),ROWS(UNIQUE(FILTER(INDEX(x,,1),(INDEX(x,,3)=G3)*(INDEX(x,,4)=H3)))))

 

[kumara_faith]

Hi Fluff,

Thank you for your response. Just to confirm, in the example of the table, this formula should be in cell H4?

 

[Fluff]

I've go it in J but you can put it where-ever you want

Fluff.xlsm
	G	H	I	J
1	Sheet 4
2	State	City	Zip Code	No of Customer
3	Montgomery	Alabama	36104	2
4	Texas	Dallas	35478	1
5	Florida	Tallahassee	87895	2
6	Missouri	Jefferson City	33658	2
Lists

Cell Formulas
Range	Formula
J3:J6	J3	=LET(a,Sheet1!$A$2:$D$100,b,Sheet2!$A$2:$D$100,c,Sheet3!$A$2:$D$100,ra,ROWS(a),rb,ra+ROWS(b),s,SEQUENCE(rb+ROWS(c)),x,IF(s<=ra,a,IF(s<=rb,INDEX(b,s-ra,{1,2,3,4}),INDEX(c,s-rb,{1,2,3,4}))),ROWS(UNIQUE(FILTER(INDEX(x,,1),(INDEX(x,,3)=G3)*(INDEX(x,,4)=H3)))))

 

[kumara_faith]

Hi Fluff,

Thank you for the solution. Appreciate your patience and have a great day ahead.

 

[Fluff]

You're welcome & thanks for the feedback.