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]

I see the usual excel numbers for a date

Do you mean you see values like 44197.09?

 

[Frank J]

yes

 

[Fluff]

In that case the only other thing I can think of, is that your Patron IDs are text & T1 is a number, or vice versa.

 

[Frank J]

In that case the only other thing I can think of, is that your Patron IDs are text & T1 is a number, or vice versa.

I just checked and they were both general, i changed them to numbers and still nothing. This has me stumped and I need to resolve it badly.

 

[Fluff]

If you put this into a blank cell what does it return?

=ISNUMBER(Q2)

As the formula works for me.

+Fluff 1.xlsm
	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	AI
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	310258222
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		Refused Name	44197.084	1	CC 101	$25	5.02	-50	2.51	44197.08	44197.09	$300	$0	$0	$300	$250	328855393	310258222
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
Summary

Cell Formulas
Range	Formula
S2:AI2	S2	=SORT(UNIQUE(FILTER(A2:Q8,(INT(I2:I8)=S1)*(Q2:Q8=T1))))
Dynamic array formulas.

 

[Frank J]

When I enter the ISNUMBER in Q2 I get a True back

When i try the formula in S2 I get #calc back again

 

[Fluff]

That error means that there's nothing matching both criteria.

 

[Frank J]

But I know there is a matching data be cause I used the data from the example I sent. Why wouldn't it match?

 

[Fluff]

Which cell in col Q matches T1?

 

[Frank J]

I got it to work since my last, now I'm trying to understand why after I made bottom row have the name & number from the top row to see if it spills the data down but i only see 1 row returned instead of 2. Won't the data filter down?

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
Data Returned vvvv
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​