Use Filter function with 2 criteria to return a specific value

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
99
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows
You appear to have changed the name of the table, which is why it's not working.
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
99
Office Version
  1. 365
Platform
  1. Windows
I checked and it seems to be the same, I uploaded an updated copy after checking on my computer
Google sheets does weird things to some excel files but i didnt change the names

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows
You are not using the formula that I posted.
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
99
Office Version
  1. 365
Platform
  1. Windows
Well, must be a bad day for me, I copied the formula again and pasted it in and now it's working fine.

Sorry for my slow mind today, thanks for all your help on this.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,270
Messages
5,657,756
Members
418,411
Latest member
Excellency

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