FILTER with multiple criteria

[StuLux]

I'm having a problem with using FILTER function with multiple criteria. I understand that to apply multiple criteria I need to place * between the criteria but I am getting #VALUE! error (each criteria works individually but not when I try both at the same time. This is my formula to try and get a filtered list of dates/times from column C without including blank rows or rows with the tile "Date Submitted" in them.

=FILTER(Data!C:C,Data!C:C<>"Date Submitted"*Data!C:C<>"")

Master File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Field 1	Ref No.	Date Submitted	Field 2	Field 3	Field 4	Questions	Answers	Field 5	Field 6	Field 7	Field 8	Field 9	Field 10
2	1	OOF-2024-5001	15/05/24 14:28:01	2	3	4	Question 1	Answer 1	5	6	7	8	9	10
3							Question 2	Answer 2
4							Question 3	Answer 3
5							Question 4	Answer 4
6							Question 5	Answer 5
7							Question 6	Answer 6
8							Question 7	Answer 7
9							Question 8	Answer 8
10							Question 9	Answer 9
11							Question 10	Answer 10
12							Question 11	Answer 11
13							Question 12	Answer 12
14							Question 13	Answer 13
15							Question 14	Answer 14
16							Question 15	Answer 15
17	Field 1	Ref No.	Date Submitted	Field 2	Field 3	Field 4	Questions	Answers	Field 5	Field 6	Field 7	Field 8	Field 9	Field 10
18	11	OOF-2024-5002	15/05/24 14:28:26	22	33	44	Question 1	Answer 20	55	66	77	88	99	1010
19							Question 2	Answer 21
20							Question 3	Answer 22
21							Question 4	Answer 23
22							Question 5	Answer 24
23							Question 6	Answer 25
24							Question 7	Answer 26
25							Question 8	Answer 27
26							Question 9	Answer 28
27							Question 10	Answer 29
28							Question 11	Answer 30
29							Question 12	Answer 31
30							Question 13	Answer 32
31							Question 14	Answer 33
32							Question 15	Answer 34
33	Field 1	Ref No.	Date Submitted	Field 2	Field 3	Field 4	Questions	Answers	Field 5	Field 6	Field 7	Field 8	Field 9	Field 10
34	111	OOF-2024-5003	15/05/24 14:34:33	222	333	444	Question 1	Answer 50	555	666	777	888	999	101010
35							Question 2	Answer 51
36							Question 3	Answer 52
37							Question 4	Answer 53
38							Question 5	Answer 54
39							Question 6	Answer 55
40							Question 7	Answer 56
41							Question 8	Answer 57
42							Question 9	Answer 58
43							Question 10	Answer 59
44							Question 11	Answer 60
45							Question 12	Answer 61
46							Question 13	Answer 62
47							Question 14	Answer 63
48							Question 15	Answer 64
Data

 

[SanjayGMusafir]

I think try this and revert

In lieu of AND we need OR option

=FILTER(Data!C:C,(Data!C:C<>"Date Submitted")+(Data!C:C<>""))

 

[Fluff]

How about

=FILTER(Data!C:C,(Data!C:C<>"Date Submitted")*(Data!C:C<>""))

 

[SanjayGMusafir]

I checked your formula, it needs minor modification to work

=FILTER(Data!C:C,(Data!C:C<>"Date Submitted")*(Data!C:C<>""))

 

[StuLux]

I think try this and revert

In lieu of AND we need OR option

=FILTER(Data!C:C,(Data!C:C<>"Date Submitted")+(Data!C:C<>""))

This results in a #SPILL! error, even though there is no cells containing anything to prevent it spilling?

 

[StuLux]

I checked your formula, it needs minor modification to work

=FILTER(Data!C:C,(Data!C:C<>"Date Submitted")*(Data!C:C<>""))

How about

=FILTER(Data!C:C,(Data!C:C<>"Date Submitted")*(Data!C:C<>""))

Thank you, this has resolved.

 

[Fluff]

This results in a #SPILL! error, even though there is no cells containing anything to prevent it spilling?

That's because it's trying to return the entire column.

Thank you, this has resolved.

Glad we could help & thanks for the feedback.

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.