Use Filter function with 2 criteria to return a specific value

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
94
Office Version
  1. 365
Platform
  1. Windows
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​
1Last NameFirst NamePitTable IDAvg. BetTime (min)Win/LossTheo. WinTime InTime OutCash InChips InFM/Mkr InTotal InTotal OutRating IDPatron ID
2AltemoAl4PJ 404$103.23
160​
0.62​
1/1/2021 2:00:001/1/2021 2:03:14$40$0$0$40$200328855521302541224
3Refused Name44197.08381CC 101$255.02
(50)​
2.51​
1/1/2021 2:00:401/1/2021 2:05:41$300$0$0$300$250328855393310258222
4Refused Name44197.084252JP 204$155.15
(85)​
1.49​
1/1/2021 2:01:191/1/2021 2:06:28$55$50$0$105$20328855433310258223
5WangLai8MB 801$800490.97
(1,700)​
4,516.89​
1/1/2021 2:02:091/1/2021 10:13:07$0$3,200$0$3,200$1,500328855490310078426
6MosleySam1CC 101$300637.73
(1,800)​
3,826.40​
1/1/2021 2:02:211/1/2021 12:40:05$1,800$500$0$2,300$500328855501310258140
7FuChang2MB 208$50114.78
(950)​
66.00​
1/1/2021 2:02:571/1/2021 3:57:44$0$19,500$0$19,500$18,550328855534303463825
8SteptoeAlbert3HH 307$2080.93
(75)​
25.47​
1/1/2021 2:03:101/1/2021 3:24:06$100$0$0$100$25328855550310229947
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,549
Office Version
  1. 365
Platform
  1. Windows
If S1 has a date, rather than date/time try
Excel Formula:
SORT(UNIQUE(FILTER(A2:Q8,(INT(I2:I8)=S1)*(Q2:Q8=T1))))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,549
Office Version
  1. 365
Platform
  1. Windows
In what way?
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
94
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

same error, #calc
I entered the formula as shown
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,549
Office Version
  1. 365
Platform
  1. Windows
What is in S1 & T1?
Also are the date/times in col I real dates, or just text that looks like dates?
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
94
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

S1 I typed in the date I want and T1 is the Patron ID to find as well
Both formatted properly
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,549
Office Version
  1. 365
Platform
  1. Windows
If you format col I as General what do you see?
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
94
Office Version
  1. 365
Platform
  1. Windows
I see the usual excel numbers for a date
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
94
Office Version
  1. 365
Platform
  1. Windows
The actual workbook I'm searching through has 200,000 entries and I can't change the date as it's linked to too many others
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top