Filter Function Not Working As Expected

[TellM1955]

I have an issue when using the filter function based on 2 sets of criteria. If I filter each piece of data independently it works fine, the issue only occurs when I include the And. Is anyone able to determine what is causing this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Ser	Route	When	PickUp-DropOff	RouteOrder					Time	Routes			#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
2	1	Hawkinge	AM	Grams/St Clares Rd	2					AM	Hawkinge			#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
3	7	Hawkinge	AM	Finch Moat /St Barrys Rd	3					PM	MerryFile			#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
4	4	Hawkinge	AM	Liverpool Rd	4						Ashford			#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
5	6	Hawkinge	AM	Archery Square	5						Chatham			#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
6	3	Hawkinge	AM	Rowing Club	6									#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
7	5	Hawkinge	AM	Goodwyn Close	7									#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
8	2	Hawkinge	AM	Garage Paris Rd	8									#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
9	8	Hawkinge	AM	Arrive	25									#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
10	9	Hawkinge	AM	Depart	1									#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
11	10	MerryFile	AM	Amble Station	1									#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
12	11	MerryFile	AM	Heckier Ewell	4
13	12	MerryFile	AM	TA Centre Amble	3
14	13	MerryFile	AM	Arrive	25
15	14	MerryFile	AM	Depart	1
16	15	Ashford	AM	Arrive	25
17	16	Ashford	Am	Depart	1
18	17	Ashford	AM	Boat Roundabout	13
19	18	Ashford	AM	North Farm Farm	5
20	19	Ashford	AM	Boundary Rd	2
21	20	Ashford	AM	South Farm	3
22	21	Ashford	AM	East Marshal	6
23	22	Chatham	AM	Dimpless Parkway	6
24	23	Chatham	AM	Spring Park	8
25	24	Chatham	AM	Joust Carpark	10
26	25	Chatham	AM	Arrive	25
27	26	Chatham	AM	Depart	1
28	28	Chatham	AM	Arrive	25
29	29	Chatham	AM	Depart	1
30	30	Ashford	PM	North Farm Farm	5
31	31	Ashford	PM	Boundary Rd	2
32	32	Ashford	PM	South Farm	3
33	33	Ashford	PM	East Marshal	6
34	34	Chatham	PM	Dimpless Parkway	4
35	35	Chatham	PM	Spring Park	7
36	36	Chatham	PM	Joust Carpark	8
37	37	Chatham	PM	Arrive	25
38	38	Chatham	PM	Depart	1
39	39	Hawkinge	PM	Arrive	25
Sheet1

Cell Formulas
Range	Formula
N1:R11	N1	=FILTER(TblRouteData6,TblRouteData6[Route]=K2)*(TblRouteData6,TblRouteData6[When]=J2)
J2:J3	J2	=UNIQUE(CHOOSECOLS(FILTER(TblRouteData6,TblRouteData6[When]<>""),3))
K2:K5	K2	=UNIQUE(TblRouteData6[Route],)
Dynamic array formulas.

 

[hagia_sofia]

It seems that there are some inaccuracies within your FILTER formula:

=FILTER(TblRouteData6,(TblRouteData6[Route]=K2)*(TblRouteData6[When]=J2))

 

[Joe4]

If you are using the Filter function, then I don't think your profile is accurate, as it shows you are using Excel 2016, and the Filter function is not available on that version.

Would you mind updating your profile to accurately reflect what version of Excel you are now on?
It is often helpful to responders to know which version you are on, so they know which functions you have access to.

Thanks

 

[TellM1955]

If you are using the Filter function, then I don't think your profile is accurate, as it shows you are using Excel 2016, and the Filter function is not available on that version.

View attachment 117576

Would you mind updating your profile to accurately reflect what version of Excel you are now on?
It is often helpful to responders to know which version you are on, so they know which functions you have access to.

Thanks

Joe, thank you for pointiing that out. I've only recently taken on 365 and hadn't updated my details.

 

[TellM1955]

Hagia, thanks for resolving that.