Filter on Multiple Criteria

[jwmi01]

Trying to filter an array based on multiple criteria. DISTRICT, VENDOR, AMOUNT. The Amount field needs to be pulled from the appropriate column based on the header value.

Trying to use the "Enter Date Here" in I9:K9 to manually identify the date that we want to match to columns A:D. I don't assume this should cause an issue, but the Data Source is on one tab and the Desired Filter Result will be generated in another tab. The goal is to use the cell values entered into K9, K10, K11 to establish the filter criteria in the formula. In all cases, only rows with $ should be filtered out - like A8:G8 shows $0 and has been omitted by the filter.

Have tried nested Filter functions, INDEX Match, helper rows etc. and can't seem to get it to work. Any suggestions are appreciated!

Example Filter File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	DATA SOURCE								DESIRED FILTER RESULT
2
3	DISTRICT	VENDOR	LAST NAME	FIRST NAME	7/24/2023	7/25/2023	7/26/2023		DISTRICT	VENDOR	LAST NAME	FIRST NAME	AMOUNT
4	1	A	SMITH	JOHN	$ 193.24	$161.00	$119.41		1	A	SMITH	JOHN	$ 161.00
5	1	B	JONES	MARY	$ 191.92	$ 60.24	$ 73.70		1	A	RUTH	BABE	$ 111.12
6	2	A	BRADY	TOM	$ 902.87	$ 50.00	$494.40		1	A	DAVIS	MILES	$ 111.12
7	2	A	BIDEN	JOE	$ 25.00	$ 50.00	$215.55		1	A	HITCHCOCK	ALFRED	$ 245.00
8	1	A	JORDAN	MICHAEL	$ 50.00	$ -	$ 59.69
9	1	D	LINCOLN	ABRAHAM	$ 276.45	$100.00	$251.79		DISTRICT		1
10	1	A	RUTH	BABE	$ 100.00	$111.12	$264.53		VENDOR		A
11	2	B	OSBOURNE	OZZY	$ 25.00	$350.00	$275.00		DATE		7/25/2023
12	2	A	LEWIS	KIM	$ 91.00	$161.00	$182.50
13	2	A	GRISHAM	JOHN	$ 132.50	$ 60.24	$ 79.85
14	4	B	ALI	MUHAMMAD	$ 31.91	$ 50.00	$189.49
15	4	C	TYSON	MIKE	$ 102.67	$ 50.00	$134.40
16	4	C	PFEIFER	MICHELLE	$ 25.00	$129.69	$142.98
17	3	A	GINGER	MARYANN	$ 245.00	$100.00	$800.00
18	1	A	DAVIS	MILES	$ 320.92	$111.12	$123.83
19	3	B	TICKY	FOWLER	$ 200.00	$350.00	$200.00
20	5	B	KING	STEVE	$ 250.00	$ 102.67	$276.17
21	5	A	TOMASI	ROLLO	$ 328.40	$ 25.00	$352.34
22	1	A	HITCHCOCK	ALFRED	$ 54.86	$ 245.00	$428.51
Sheet1

 

[jwmi01]

Sorry...disregard "Enter Date Here" in I9:K9". I edited the example file so that the criteria are entered into K9, 10, and 11.

Thanks!

 

[Peter_SSs]

See if this works for you.

23 07 23.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	DATA SOURCE								DESIRED FILTER RESULT
2
3	DISTRICT	VENDOR	LAST NAME	FIRST NAME	7/24/2023	7/25/2023	7/26/2023		DISTRICT	VENDOR	LAST NAME	FIRST NAME	AMOUNT
4	1	A	SMITH	JOHN	193.24	161	119.41		1	A	SMITH	JOHN	161
5	1	B	JONES	MARY	191.92	60.24	73.7		1	A	RUTH	BABE	111.12
6	2	A	BRADY	TOM	902.87	50	494.4		1	A	DAVIS	MILES	111.12
7	2	A	BIDEN	JOE	25	50	215.55		1	A	HITCHCOCK	ALFRED	245
8	1	A	JORDAN	MICHAEL	50	0	59.69
9	1	D	LINCOLN	ABRAHAM	276.45	100	251.79		DISTRICT		1
10	1	A	RUTH	BABE	100	111.12	264.53		VENDOR		A
11	2	B	OSBOURNE	OZZY	25	350	275		DATE		7/25/2023
12	2	A	LEWIS	KIM	91	161	182.5
13	2	A	GRISHAM	JOHN	132.5	60.24	79.85
14	4	B	ALI	MUHAMMAD	31.91	50	189.49
15	4	C	TYSON	MIKE	102.67	50	134.4
16	4	C	PFEIFER	MICHELLE	25	129.69	142.98
17	3	A	GINGER	MARYANN	245	100	800
18	1	A	DAVIS	MILES	320.92	111.12	123.83
19	3	B	TICKY	FOWLER	200	350	200
20	5	B	KING	STEVE	250	102.67	276.17
21	5	A	TOMASI	ROLLO	328.4	25	352.34
22	1	A	HITCHCOCK	ALFRED	54.86	245	428.51
Filter

Cell Formulas
Range	Formula
I4:L7	I4	=FILTER(A4:D22,(A4:A22=K9)*(B4:B22=K10)*(INDEX(E4:G22,0,MATCH(K11,E3:G3,0))>0))
M4:M7	M4	=LET(f,FILTER(E4:G22,E3:G3=K11),FILTER(f,(A4:A22=K9)*(B4:B22=K10)*(f>0)))
Dynamic array formulas.

 

[jwmi01]

This works perfectly.

Thanks so much for the help Peter, solution will save us significant processing time!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.