Using a formula that populate specific list based on list of conditions

[zinah]

Hi,

I have below sample table and what I need is to populate demographics based on the count of each factor.
I put the expected results based on the result of factor count of AAA, which is in this example are 4 that is based on the formula that count AAA based on selected filters from drop down list. The table of Factors and their count is a dynamic table that depends on selected items from Column A (USA, Salse, etc...). Can anyone help?
As you can notice, the data are in different columns and not specific sequence and in my case below, I need to get the result of 2 cells in one cell (first & last name) and CustomerKey after column Status (Y/N)

Test_Filter FIle.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1												CustomerKey		FirstName	LastName			Country	Status (Y/N)					BirthDate	MaritalStatus		Team						Factors
2												11000		JON	YANG			USA	Yes					4/8/1966	M		Sales						AAA
3												11001		EUGENE	HUANG			UK	Yes					########	S		Sales						BBB
4	USA											11002		RUBEN	TORRES			USA	Yes					########	M		HR						AAA
5												11003		CHRISTY	ZHU			USA	Yes					########	S		HR						BBB
6	Sales											11004		ELIZABETH	JOHNSON			USA	Yes					8/8/1968	S		Sales						BBB
7												11005		JULIO	RUIZ			UAE	Yes					8/5/1965	S		Sales						HHH
8	Include											11007		MARCO	MEHTA			USA	Yes					5/9/1964	M		HR						BBB
9												11008		ROBIN	VERHOFF			UK	Yes					7/7/1964	S		HR						BBB
10	Yes											11009		SHANNON	CARLSON			USA	Yes					4/1/1964	S		Sales						BBB
11			Factors	Count of each factor								11010		JACQUELYN	SUAREZ			USA	Yes					2/6/1964	S		Sales						AAA
12			AAA	4								11011		CURTIS	LU			UK	Yes					########	M		HR						BBB
13			BBB	3								11012		LAUREN	WALKER			UK	No					########	M		HR						HHH
14			HHH	1								11013		IAN	JENKINS			USA	Yes					8/6/1968	M		Sales						AAA
15												11014		SYDNEY	BENNETT			UK	Yes					5/9/1968	S		Sales						BBB
16												11015		CHLOE	YOUNG			USA	Yes					########	S		HR						AAA
17												11016		WYATT	HILL			USA	No					########	M		HR						AAA
18												11017		SHANNON	WANG			UK	Yes					########	S		Sales						BBB
19			Expected Result									11018		CLARENCE	RAI			UAE	Yes					########	S		Sales						HHH
20			FirstName LastName	Country	Status (Y/N)	CustomerKey	BirthDate	MaritalStatus	Team	Factors		11019		LUKE	LAL			UK	Yes					3/7/1978	S		HR						AAA
21			JON YANG	USA	Yes	11000	4/8/1966	M	Sales	AAA		11020		JORDAN	KING			UK	Yes					########	S		HR						AAA
22			JACQUELYN SUAREZ	USA	Yes	11010	2/6/1964	S	Sales	AAA		11021		DESTINY	WILSON			UAE	Yes					9/3/1978	S		Sales						HHH
23			IAN JENKINS	USA	Yes	11013	8/6/1968	M	Sales	AAA		11022		ETHAN	ZHANG			UK	Yes					########	M		Sales						AAA
24			CHLOE GARCIA	USA	Yes	11037	11/27/1977	S	Sales	AAA		11023		SETH	EDWARDS			UK	Yes					########	M		HR						BBB
25												11024		RUSSELL	XIE			UAE	Yes					########	M		HR						HHH
26												11025		ALEJANDRO	BECK			UAE	Yes					########	M		Sales						HHH
27												11026		HAROLD	SAI			USA	Yes					4/3/1946	S		Sales						BBB
28												11027		JESSIE	ZHAO			UK	No					########	M		HR						HHH
29												11028		JILL	JIMENEZ			USA	No					########	M		HR						AAA
30												11029		JIMMY	MORENO			UK	Yes					########	M		Sales						BBB
31												11030		BETHANY	YUAN			UAE	Yes					########	M		Sales						HHH
32												11031		THERESA	RAMOS			UK	Yes					########	M		HR						HHH
33												11032		DENISE	STONE			UK	No					########	M		HR						BBB
34												11033		JAIME	NATH			UK	Yes					########	M		Sales						HHH
35												11034		EBONY	GONZALEZ			UAE	Yes					########	M		HR						HHH
36												11035		WENDY	DOMINGUEZ			UK	No					########	M		HR						BBB
37												11036		JENNIFER	RUSSELL			USA	No					########	M		HR						HHH
38												11037		CHLOE	GARCIA			USA	Yes					########	S		Sales						AAA
39												11038		DIANA	HERNANDEZ			USA	Yes					########	M		Sales						HHH
40												11039		MARC	MARTIN			UAE	Yes					########	M		Sales						HHH
41												11040		JESSE	MURPHY			USA	No					8/1/1977	M		Sales						AAA
42												11041		AMANDA	CARTER			USA	Yes					########	M		Sales						BBB
43												11042		MEGAN	SANCHEZ			UAE	Yes					########	M		Sales						HHH
44												11043		NATHAN	SIMMONS			USA	No					########	M		Sales						AAA
45												11044		ADAM	FLORES			USA	No					########	M		Sales						BBB
46												11045		LEONARD	NARA			UAE	Yes					########	S		Sales						BBB
Sheet1

Cell Formulas
Range	Formula
D12:D14	D12	=COUNTIFS($AG:$AG,C12,$R:$R,$A$4,$AA:$AA,$A$6,$DT:$DT,$A$8,$S:$S,$A$10)

Cells with Data Validation
Cell	Allow	Criteria
A10	List	Yes,No
A8	List	Include,Exclude
A6	List	Sales,HR
A4	List	USA,UK,UAE

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L		N	O			R	S					X	Y		AA						AG
1												CustomerKey		FirstName	LastName			Country	Status (Y/N)					BirthDate	MaritalStatus		Team						Factors
2												11000		JON	YANG			USA	Yes					08/04/1966	M		Sales						AAA
3												11001		EUGENE	HUANG			UK	Yes					14/05/1965	S		Sales						BBB
4	USA											11002		RUBEN	TORRES			USA	Yes					12/08/1965	M		HR						AAA
5												11003		CHRISTY	ZHU			USA	Yes					15/02/1968	S		HR						BBB
6	Sales											11004		ELIZABETH	JOHNSON			USA	Yes					08/08/1968	S		Sales						BBB
7												11005		JULIO	RUIZ			UAE	Yes					05/08/1965	S		Sales						HHH
8	Include											11007		MARCO	MEHTA			USA	Yes					09/05/1964	M		HR						BBB
9												11008		ROBIN	VERHOFF			UK	Yes					07/07/1964	S		HR						BBB
10	Yes											11009		SHANNON	CARLSON			USA	Yes					01/04/1964	S		Sales						BBB
11			Factors	Count of each factor								11010		JACQUELYN	SUAREZ			USA	Yes					06/02/1964	S		Sales						AAA
12			AAA	4								11011		CURTIS	LU			UK	Yes					04/11/1963	M		HR						BBB
13			BBB	3								11012		LAUREN	WALKER			UK	No					18/01/1968	M		HR						HHH
14			HHH	1								11013		IAN	JENKINS			USA	Yes					06/08/1968	M		Sales						AAA
15												11014		SYDNEY	BENNETT			UK	Yes					09/05/1968	S		Sales						BBB
16												11015		CHLOE	YOUNG			USA	Yes					27/02/1979	S		HR						AAA
17												11016		WYATT	HILL			USA	No					28/04/1979	M		HR						AAA
18												11017		SHANNON	WANG			UK	Yes					26/06/1944	S		Sales						BBB
19												11018		CLARENCE	RAI			UAE	Yes					09/10/1944	S		Sales						HHH
20		FirstName	LastName	Country	Status (Y/N)	CustomerKey	BirthDate	MaritalStatus	Team	Factors		11019		LUKE	LAL			UK	Yes					07/03/1978	S		HR						AAA
21		JON	YANG	USA	Yes	11000	08/04/1966	M	Sales	AAA		11020		JORDAN	KING			UK	Yes					20/09/1978	S		HR						AAA
22		ELIZABETH	JOHNSON	USA	Yes	11004	08/08/1968	S	Sales	BBB		11021		DESTINY	WILSON			UAE	Yes					03/09/1978	S		Sales						HHH
23		SHANNON	CARLSON	USA	Yes	11009	01/04/1964	S	Sales	BBB		11022		ETHAN	ZHANG			UK	Yes					12/10/1978	M		Sales						AAA
24		JACQUELYN	SUAREZ	USA	Yes	11010	06/02/1964	S	Sales	AAA		11023		SETH	EDWARDS			UK	Yes					11/10/1978	M		HR						BBB
25		IAN	JENKINS	USA	Yes	11013	06/08/1968	M	Sales	AAA		11024		RUSSELL	XIE			UAE	Yes					17/09/1978	M		HR						HHH
26		HAROLD	SAI	USA	Yes	11026	03/04/1946	S	Sales	BBB		11025		ALEJANDRO	BECK			UAE	Yes					23/12/1945	M		Sales						HHH
27		CHLOE	GARCIA	USA	Yes	11037	27/11/1977	S	Sales	AAA		11026		HAROLD	SAI			USA	Yes					03/04/1946	S		Sales						BBB
28		DIANA	HERNANDEZ	USA	Yes	11038	23/03/1948	M	Sales	HHH		11027		JESSIE	ZHAO			UK	No					07/12/1946	M		HR						HHH
29												11028		JILL	JIMENEZ			USA	No					11/04/1946	M		HR						AAA
30												11029		JIMMY	MORENO			UK	Yes					21/12/1946	M		Sales						BBB
31												11030		BETHANY	YUAN			UAE	Yes					22/02/1947	M		Sales						HHH
32												11031		THERESA	RAMOS			UK	Yes					22/08/1947	M		HR						HHH
33												11032		DENISE	STONE			UK	No					11/06/1947	M		HR						BBB
34												11033		JAIME	NATH			UK	Yes					23/09/1947	M		Sales						HHH
35												11034		EBONY	GONZALEZ			UAE	Yes					19/06/1947	M		HR						HHH
36												11035		WENDY	DOMINGUEZ			UK	No					24/02/1948	M		HR						BBB
37												11036		JENNIFER	RUSSELL			USA	No					18/12/1978	M		HR						HHH
38												11037		CHLOE	GARCIA			USA	Yes					27/11/1977	S		Sales						AAA
39												11038		DIANA	HERNANDEZ			USA	Yes					23/03/1948	M		Sales						HHH
40												11039		MARC	MARTIN			UAE	Yes					17/12/1948	M		Sales						HHH
41												11040		JESSE	MURPHY			USA	No					01/08/1977	M		Sales						AAA
42												11041		AMANDA	CARTER			USA	Yes					16/10/1977	M		Sales						BBB
43												11042		MEGAN	SANCHEZ			UAE	Yes					13/06/1977	M		Sales						HHH
44												11043		NATHAN	SIMMONS			USA	No					24/02/1976	M		Sales						AAA
45												11044		ADAM	FLORES			USA	No					24/05/1949	M		Sales						BBB
46												11045		LEONARD	NARA			UAE	Yes					19/05/1950	S		Sales						BBB
Main

Cell Formulas
Range	Formula
D12:D14	D12	=COUNTIFS($AG:$AG,C12,$R:$R,$A$4,$AA:$AA,$A$6,$DT:$DT,$A$8,$S:$S,$A$10)
B21:J28	B21	=SORTBY(FILTER(FILTER(L1:DT100,(R1:R100=A4)*(AA1:AA100=A6)*(S1:S100=A10)*(DT1:DT100=A8)),ISNUMBER(MATCH(L1:DT1,B20:J20,0))),MATCH(FILTER(L1:DT1,ISNUMBER(MATCH(L1:DT1,B20:J20,0))),B20:J20,0))
Dynamic array formulas.

 

[zinah]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L		N	O			R	S					X	Y		AA						AG
1												CustomerKey		FirstName	LastName			Country	Status (Y/N)					BirthDate	MaritalStatus		Team						Factors
2												11000		JON	YANG			USA	Yes					08/04/1966	M		Sales						AAA
3												11001		EUGENE	HUANG			UK	Yes					14/05/1965	S		Sales						BBB
4	USA											11002		RUBEN	TORRES			USA	Yes					12/08/1965	M		HR						AAA
5												11003		CHRISTY	ZHU			USA	Yes					15/02/1968	S		HR						BBB
6	Sales											11004		ELIZABETH	JOHNSON			USA	Yes					08/08/1968	S		Sales						BBB
7												11005		JULIO	RUIZ			UAE	Yes					05/08/1965	S		Sales						HHH
8	Include											11007		MARCO	MEHTA			USA	Yes					09/05/1964	M		HR						BBB
9												11008		ROBIN	VERHOFF			UK	Yes					07/07/1964	S		HR						BBB
10	Yes											11009		SHANNON	CARLSON			USA	Yes					01/04/1964	S		Sales						BBB
11			Factors	Count of each factor								11010		JACQUELYN	SUAREZ			USA	Yes					06/02/1964	S		Sales						AAA
12			AAA	4								11011		CURTIS	LU			UK	Yes					04/11/1963	M		HR						BBB
13			BBB	3								11012		LAUREN	WALKER			UK	No					18/01/1968	M		HR						HHH
14			HHH	1								11013		IAN	JENKINS			USA	Yes					06/08/1968	M		Sales						AAA
15												11014		SYDNEY	BENNETT			UK	Yes					09/05/1968	S		Sales						BBB
16												11015		CHLOE	YOUNG			USA	Yes					27/02/1979	S		HR						AAA
17												11016		WYATT	HILL			USA	No					28/04/1979	M		HR						AAA
18												11017		SHANNON	WANG			UK	Yes					26/06/1944	S		Sales						BBB
19												11018		CLARENCE	RAI			UAE	Yes					09/10/1944	S		Sales						HHH
20		FirstName	LastName	Country	Status (Y/N)	CustomerKey	BirthDate	MaritalStatus	Team	Factors		11019		LUKE	LAL			UK	Yes					07/03/1978	S		HR						AAA
21		JON	YANG	USA	Yes	11000	08/04/1966	M	Sales	AAA		11020		JORDAN	KING			UK	Yes					20/09/1978	S		HR						AAA
22		ELIZABETH	JOHNSON	USA	Yes	11004	08/08/1968	S	Sales	BBB		11021		DESTINY	WILSON			UAE	Yes					03/09/1978	S		Sales						HHH
23		SHANNON	CARLSON	USA	Yes	11009	01/04/1964	S	Sales	BBB		11022		ETHAN	ZHANG			UK	Yes					12/10/1978	M		Sales						AAA
24		JACQUELYN	SUAREZ	USA	Yes	11010	06/02/1964	S	Sales	AAA		11023		SETH	EDWARDS			UK	Yes					11/10/1978	M		HR						BBB
25		IAN	JENKINS	USA	Yes	11013	06/08/1968	M	Sales	AAA		11024		RUSSELL	XIE			UAE	Yes					17/09/1978	M		HR						HHH
26		HAROLD	SAI	USA	Yes	11026	03/04/1946	S	Sales	BBB		11025		ALEJANDRO	BECK			UAE	Yes					23/12/1945	M		Sales						HHH
27		CHLOE	GARCIA	USA	Yes	11037	27/11/1977	S	Sales	AAA		11026		HAROLD	SAI			USA	Yes					03/04/1946	S		Sales						BBB
28		DIANA	HERNANDEZ	USA	Yes	11038	23/03/1948	M	Sales	HHH		11027		JESSIE	ZHAO			UK	No					07/12/1946	M		HR						HHH
29												11028		JILL	JIMENEZ			USA	No					11/04/1946	M		HR						AAA
30												11029		JIMMY	MORENO			UK	Yes					21/12/1946	M		Sales						BBB
31												11030		BETHANY	YUAN			UAE	Yes					22/02/1947	M		Sales						HHH
32												11031		THERESA	RAMOS			UK	Yes					22/08/1947	M		HR						HHH
33												11032		DENISE	STONE			UK	No					11/06/1947	M		HR						BBB
34												11033		JAIME	NATH			UK	Yes					23/09/1947	M		Sales						HHH
35												11034		EBONY	GONZALEZ			UAE	Yes					19/06/1947	M		HR						HHH
36												11035		WENDY	DOMINGUEZ			UK	No					24/02/1948	M		HR						BBB
37												11036		JENNIFER	RUSSELL			USA	No					18/12/1978	M		HR						HHH
38												11037		CHLOE	GARCIA			USA	Yes					27/11/1977	S		Sales						AAA
39												11038		DIANA	HERNANDEZ			USA	Yes					23/03/1948	M		Sales						HHH
40												11039		MARC	MARTIN			UAE	Yes					17/12/1948	M		Sales						HHH
41												11040		JESSE	MURPHY			USA	No					01/08/1977	M		Sales						AAA
42												11041		AMANDA	CARTER			USA	Yes					16/10/1977	M		Sales						BBB
43												11042		MEGAN	SANCHEZ			UAE	Yes					13/06/1977	M		Sales						HHH
44												11043		NATHAN	SIMMONS			USA	No					24/02/1976	M		Sales						AAA
45												11044		ADAM	FLORES			USA	No					24/05/1949	M		Sales						BBB
46												11045		LEONARD	NARA			UAE	Yes					19/05/1950	S		Sales						BBB
Main

Cell Formulas
Range	Formula
D12:D14	D12	=COUNTIFS($AG:$AG,C12,$R:$R,$A$4,$AA:$AA,$A$6,$DT:$DT,$A$8,$S:$S,$A$10)
B21:J28	B21	=SORTBY(FILTER(FILTER(L1:DT100,(R1:R100=A4)*(AA1:AA100=A6)*(S1:S100=A10)*(DT1:DT100=A8)),ISNUMBER(MATCH(L1:DT1,B20:J20,0))),MATCH(FILTER(L1:DT1,ISNUMBER(MATCH(L1:DT1,B20:J20,0))),B20:J20,0))
Dynamic array formulas.

As usual you're the first to suggest a great array formula solution, thank you for that. Your suggestion works and I even understood the methdology behind your formula and added another condition to filter the result(as showing in formula below), thank you so much

=IF(D13<>0,SORTBY(FILTER(FILTER(O1:DW100,(AJ1:AJ100=C13)*(U1:U100=A4)*(AD1:AD100=A6)*(V1:V100=A10)*(DW1:DW100=A8)),ISNUMBER(MATCH(O1:DW1,B20:J20,0))),MATCH(FILTER(O1:DW1,ISNUMBER(MATCH(O1:DW1,B20:J20,0))),B20:J20,0)),"")

 

[Fluff]

You're welcome & thanks for the feedback.