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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows
Did you expand the range to take into account the new row?
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRST
1Last NameFirst NamePitTable IDAvg. BetTime (min)Win/LossTheo. WinTime InTime OutCash InChips InFM/Mkr InTotal InTotal OutRating IDPatron ID01/01/2021302541224
2AltemoAl4PJ 404$103.231600.6201/01/2021 02:0001/01/2021 02:03$40$0$0$40$200328855521302541224
3Refused Name44197.08381CC 101$255.02-502.5101/01/2021 02:0001/01/2021 02:05$300$0$0$300$250328855393310258222
4Refused Name44197.084252JP 204$155.15-851.4901/01/2021 02:0101/01/2021 02:06$55$50$0$105$20328855433310258223
5WangLai8MB 801$800490.97-1,7004,516.8901/01/2021 02:0201/01/2021 10:13$0$3,200$0$3,200$1,500328855490310078426
6MosleySam1CC 101$300637.73-1,8003,826.4001/01/2021 02:0201/01/2021 12:40$1,800$500$0$2,300$500328855501310258140
7FuChang2MB 208$50114.78-9506601/01/2021 02:0201/01/2021 03:57$0$19,500$0$19,500$18,550328855534303463825
8SteptoeAlbert3HH 307$2080.93-7525.4701/01/2021 02:0301/01/2021 03:24$100$0$0$100$25328855550310229947
9AltemoAl4PJ 404$103.231600.6201/01/2021 02:0301/01/2021 02:03$40$0$0$40$200328855521302541224
10
11AltemoAl4PJ 404$103.231600.6201/01/2021 02:0001/01/2021 02:03$40$0$0$40$200328855521302541224
12AltemoAl4PJ 404$103.231600.6201/01/2021 02:0301/01/2021 02:03$40$0$0$40$200328855521302541224
13
Summary
Cell Formulas
RangeFormula
A11:Q12A11=SORT(UNIQUE(FILTER(A2:Q9,(INT(I2:I9)=S1)*(Q2:Q9=T1))))
Dynamic array formulas.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
99
Office Version
  1. 365
Platform
  1. Windows
RangeFormula
Cell Formulas
A11:Q12A11=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows
Yes you can do it like
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRST
1Last NameFirst NamePitTable IDAvg. BetTime (min)Win/LossTheo. WinTime InTime OutCash InChips InFM/Mkr InTotal InTotal OutRating IDPatron ID01/01/2021302541224
2AltemoAl4PJ 404$103.231600.6201/01/2021 02:0001/01/2021 02:03$40$0$0$40$200328855521302541224
3Refused Name44197.08381CC 101$255.02-502.5101/01/2021 02:0001/01/2021 02:05$300$0$0$300$250328855393310258222
4Refused Name44197.084252JP 204$155.15-851.4901/01/2021 02:0101/01/2021 02:06$55$50$0$105$20328855433310258223
5WangLai8MB 801$800490.97-1,7004,516.8901/01/2021 02:0201/01/2021 10:13$0$3,200$0$3,200$1,500328855490310078426
6MosleySam1CC 101$300637.73-1,8003,826.4001/01/2021 02:0201/01/2021 12:40$1,800$500$0$2,300$500328855501310258140
7FuChang2MB 208$50114.78-9506601/01/2021 02:0201/01/2021 03:57$0$19,500$0$19,500$18,550328855534303463825
8SteptoeAlbert3HH 307$2080.93-7525.4701/01/2021 02:0301/01/2021 03:24$100$0$0$100$25328855550310229947
9AltemoAl4PJ 404$103.231600.6201/01/2021 02:0301/01/2021 02:03$40$0$0$40$200328855521302541224
10
11AltemoAl4PJ 404$103.231600.6201/01/2021 02:0001/01/2021 02:03$40$0$0$40$200328855521302541224
12AltemoAl4PJ 404$103.231600.6201/01/2021 02:0301/01/2021 02:03$40$0$0$40$200328855521302541224
Summary
Cell Formulas
RangeFormula
A11:Q12A11=SORT(UNIQUE(FILTER(Table1,(INT(Table1[Time In])=S1)*(Table1[Patron ID]=T1))))
Dynamic array formulas.
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
99
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
99
Office Version
  1. 365
Platform
  1. Windows
But it's not for me, see my updated last row as I stated before

Last NameFirst NamePitTable IDAvg. BetTime (min)Win/LossTheo. WinTime InTime OutCash InChips InFM/Mkr InTotal InTotal OutRating IDPatron ID
AltemoAl4PJ 404$103.23
160​
0.62​
1/1/2021 2:00:001/1/2021 2:03:14$40$0$0$40$200328855521302541224
Refused Name44197.08381CC 101$255.02
(50)​
2.51​
1/1/2021 2:00:401/1/2021 2:05:41$300$0$0$300$250328855393310258222
Refused Name44197.084252JP 204$155.15
(85)​
1.49​
1/1/2021 2:01:191/1/2021 2:06:28$55$50$0$105$20328855433310258223
WangLai8MB 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
MosleySam1CC 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
FuChang2MB 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
AltemoAl3HH 307$2080.93
(75)​
25.47​
1/1/2021 2:03:101/1/2021 3:24:06$100$0$0$100$25328855550302541224
AltemoAl4PJ 404
10​
3.23​
160​
0.62​
1/1/21 2:001/1/21 2:03
40​
0​
0​
40​
200​
328855521​
302541224​
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
99
Office Version
  1. 365
Platform
  1. Windows
I checked all the cells for formatting and any spaces but they all match correctly.
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
99
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,765
Members
418,412
Latest member
fehr56

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