Use Filter function with 2 criteria to return a specific value

[Frank J]

I've spent many hours trying to find out why the basic formula for Filter function with 2 criteria will not return a value other than #CALC.
I'm trying to filter the data below for a specific Patron using the Patron ID and a specific Rating by the Date it occurred using the Time In
The data is in a Table and I've shown a sample below, the Rows/Columns are added for reference.

The formula I've tried many variants of is: SORT(UNIQUE(FILTER(A2:Q8,(I2:I8=S1)*(Q2:Q8=T1))))

S1 is the Date and T1 is the Patron ID, I want to be able to use these cells to look at different players as needed.

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​
1	Last Name	First Name	Pit	Table ID	Avg. Bet	Time (min)	Win/Loss	Theo. Win	Time In	Time Out	Cash In	Chips In	FM/Mkr In	Total In	Total Out	Rating ID	Patron ID
2	Altemo	Al	4	PJ 404	$10	3.23	160​	0.62​	1/1/2021 2:00:00	1/1/2021 2:03:14	$40	$0	$0	$40	$200	328855521	302541224
3	Refused Name	44197.0838	1	CC 101	$25	5.02	(50)​	2.51​	1/1/2021 2:00:40	1/1/2021 2:05:41	$300	$0	$0	$300	$250	328855393	310258222
4	Refused Name	44197.08425	2	JP 204	$15	5.15	(85)​	1.49​	1/1/2021 2:01:19	1/1/2021 2:06:28	$55	$50	$0	$105	$20	328855433	310258223
5	Wang	Lai	8	MB 801	$800	490.97	(1,700)​	4,516.89​	1/1/2021 2:02:09	1/1/2021 10:13:07	$0	$3,200	$0	$3,200	$1,500	328855490	310078426
6	Mosley	Sam	1	CC 101	$300	637.73	(1,800)​	3,826.40​	1/1/2021 2:02:21	1/1/2021 12:40:05	$1,800	$500	$0	$2,300	$500	328855501	310258140
7	Fu	Chang	2	MB 208	$50	114.78	(950)​	66.00​	1/1/2021 2:02:57	1/1/2021 3:57:44	$0	$19,500	$0	$19,500	$18,550	328855534	303463825
8	Steptoe	Albert	3	HH 307	$20	80.93	(75)​	25.47​	1/1/2021 2:03:10	1/1/2021 3:24:06	$100	$0	$0	$100	$25	328855550	310229947

 

[Fluff]

Did you expand the range to take into account the new row?

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Last Name	First Name	Pit	Table ID	Avg. Bet	Time (min)	Win/Loss	Theo. Win	Time In	Time Out	Cash In	Chips In	FM/Mkr In	Total In	Total Out	Rating ID	Patron ID		01/01/2021	302541224
2	Altemo	Al	4	PJ 404	$10	3.23	160	0.62	01/01/2021 02:00	01/01/2021 02:03	$40	$0	$0	$40	$200	328855521	302541224
3	Refused Name	44197.0838	1	CC 101	$25	5.02	-50	2.51	01/01/2021 02:00	01/01/2021 02:05	$300	$0	$0	$300	$250	328855393	310258222
4	Refused Name	44197.08425	2	JP 204	$15	5.15	-85	1.49	01/01/2021 02:01	01/01/2021 02:06	$55	$50	$0	$105	$20	328855433	310258223
5	Wang	Lai	8	MB 801	$800	490.97	-1,700	4,516.89	01/01/2021 02:02	01/01/2021 10:13	$0	$3,200	$0	$3,200	$1,500	328855490	310078426
6	Mosley	Sam	1	CC 101	$300	637.73	-1,800	3,826.40	01/01/2021 02:02	01/01/2021 12:40	$1,800	$500	$0	$2,300	$500	328855501	310258140
7	Fu	Chang	2	MB 208	$50	114.78	-950	66	01/01/2021 02:02	01/01/2021 03:57	$0	$19,500	$0	$19,500	$18,550	328855534	303463825
8	Steptoe	Albert	3	HH 307	$20	80.93	-75	25.47	01/01/2021 02:03	01/01/2021 03:24	$100	$0	$0	$100	$25	328855550	310229947
9	Altemo	Al	4	PJ 404	$10	3.23	160	0.62	01/01/2021 02:03	01/01/2021 02:03	$40	$0	$0	$40	$200	328855521	302541224
10
11	Altemo	Al	4	PJ 404	$10	3.23	160	0.62	01/01/2021 02:00	01/01/2021 02:03	$40	$0	$0	$40	$200	328855521	302541224
12	Altemo	Al	4	PJ 404	$10	3.23	160	0.62	01/01/2021 02:03	01/01/2021 02:03	$40	$0	$0	$40	$200	328855521	302541224
13
Summary

Cell Formulas
Range	Formula
A11:Q12	A11	=SORT(UNIQUE(FILTER(A2:Q9,(INT(I2:I9)=S1)*(Q2:Q9=T1))))
Dynamic array formulas.

 

[Frank J]

Range	Formula
Cell Formulas
A11:Q12	A11	=SORT(UNIQUE(FILTER(A2:Q9,(INT(I2:I9)=S1)*(Q2:Q9=T1))))
Dynamic array formulas.

Where is that in the formula? Can I get this to spill over and include all relevant cells/rows? If I point it at a data table and select a whole column of it instead of the cell ranges will that work?

 

[Fluff]

Yes you can do it like

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Last Name	First Name	Pit	Table ID	Avg. Bet	Time (min)	Win/Loss	Theo. Win	Time In	Time Out	Cash In	Chips In	FM/Mkr In	Total In	Total Out	Rating ID	Patron ID		01/01/2021	302541224
2	Altemo	Al	4	PJ 404	$10	3.23	160	0.62	01/01/2021 02:00	01/01/2021 02:03	$40	$0	$0	$40	$200	328855521	302541224
3	Refused Name	44197.0838	1	CC 101	$25	5.02	-50	2.51	01/01/2021 02:00	01/01/2021 02:05	$300	$0	$0	$300	$250	328855393	310258222
4	Refused Name	44197.08425	2	JP 204	$15	5.15	-85	1.49	01/01/2021 02:01	01/01/2021 02:06	$55	$50	$0	$105	$20	328855433	310258223
5	Wang	Lai	8	MB 801	$800	490.97	-1,700	4,516.89	01/01/2021 02:02	01/01/2021 10:13	$0	$3,200	$0	$3,200	$1,500	328855490	310078426
6	Mosley	Sam	1	CC 101	$300	637.73	-1,800	3,826.40	01/01/2021 02:02	01/01/2021 12:40	$1,800	$500	$0	$2,300	$500	328855501	310258140
7	Fu	Chang	2	MB 208	$50	114.78	-950	66	01/01/2021 02:02	01/01/2021 03:57	$0	$19,500	$0	$19,500	$18,550	328855534	303463825
8	Steptoe	Albert	3	HH 307	$20	80.93	-75	25.47	01/01/2021 02:03	01/01/2021 03:24	$100	$0	$0	$100	$25	328855550	310229947
9	Altemo	Al	4	PJ 404	$10	3.23	160	0.62	01/01/2021 02:03	01/01/2021 02:03	$40	$0	$0	$40	$200	328855521	302541224
10
11	Altemo	Al	4	PJ 404	$10	3.23	160	0.62	01/01/2021 02:00	01/01/2021 02:03	$40	$0	$0	$40	$200	328855521	302541224
12	Altemo	Al	4	PJ 404	$10	3.23	160	0.62	01/01/2021 02:03	01/01/2021 02:03	$40	$0	$0	$40	$200	328855521	302541224
Summary

Cell Formulas
Range	Formula
A11:Q12	A11	=SORT(UNIQUE(FILTER(Table1,(INT(Table1[Time In])=S1)*(Table1[Patron ID]=T1))))
Dynamic array formulas.

 

[Frank J]

Where would I see the Range you show A11:Q12? Can the formula spill like a sequence formula or a plain filter formula? That's what I expected it to do.

 

[Fluff]

The formula is in A11 & spills into as many cells as needed.

 

[Frank J]

But it's not for me, see my updated last row as I stated before

Last Name	First Name	Pit	Table ID	Avg. Bet	Time (min)	Win/Loss	Theo. Win	Time In	Time Out	Cash In	Chips In	FM/Mkr In	Total In	Total Out	Rating ID	Patron ID
Altemo	Al	4	PJ 404	$10	3.23	160​	0.62​	1/1/2021 2:00:00	1/1/2021 2:03:14	$40	$0	$0	$40	$200	328855521	302541224
Refused Name	44197.0838	1	CC 101	$25	5.02	(50)​	2.51​	1/1/2021 2:00:40	1/1/2021 2:05:41	$300	$0	$0	$300	$250	328855393	310258222
Refused Name	44197.08425	2	JP 204	$15	5.15	(85)​	1.49​	1/1/2021 2:01:19	1/1/2021 2:06:28	$55	$50	$0	$105	$20	328855433	310258223
Wang	Lai	8	MB 801	$800	490.97	(1,700)​	4,516.89​	1/1/2021 2:02:09	1/1/2021 10:13:07	$0	$3,200	$0	$3,200	$1,500	328855490	310078426
Mosley	Sam	1	CC 101	$300	637.73	(1,800)​	3,826.40​	1/1/2021 2:02:21	1/1/2021 12:40:05	$1,800	$500	$0	$2,300	$500	328855501	310258140
Fu	Chang	2	MB 208	$50	114.78	(950)​	66.00​	1/1/2021 2:02:57	1/1/2021 3:57:44	$0	$19,500	$0	$19,500	$18,550	328855534	303463825
Altemo	Al	3	HH 307	$20	80.93	(75)​	25.47​	1/1/2021 2:03:10	1/1/2021 3:24:06	$100	$0	$0	$100	$25	328855550	302541224
Altemo	Al	4	PJ 404	10​	3.23​	160​	0.62​	1/1/21 2:00	1/1/21 2:03	40​	0​	0​	40​	200​	328855521​	302541224​

 

[Fluff]

In that case it's either mismatched data (ie text and numbers) or maybe leading/trailing spaces.

 

[Frank J]

I checked all the cells for formatting and any spaces but they all match correctly.

 

[Frank J]

I did an experiment and added another row, gave it a different date and then changed the date in S1. It returned a #calc.

 

[Fluff]

In that case can you upload your workbook to a share site, such as OneDrive, Google DRive, DropBox. Mark for sharing & post the link you get to the thread.