Add and (+) conditions to or FILTER criteria

[ollyhughes1982]

Hi,

I have the below formula:

=IFERROR(FILTER('All Completed Runs'!C4:C2003,('All Completed Runs'!EC4:EC2003<>"")*('All Completed Runs'!FL4:FL2003<>1)*('All Completed Runs'!D4:D2003="Singapore")),"")

I want to tag onto the end the function two extra criteria; the result should only be positive if:

• The year is greater than 2019
• The year is less than 2023

(i.e. The year is either 2020, 2021 or 2022) 'All Completed Runs'!AJ is the Year column.

I have tagged the "and (+)" conditions *(('All Completed Runs'!AJ4:AJ2003>2019)+(‘All Completed Runs'!AJ4:AJ2003<2023)) on to the end. Is this correct? Amended function below:

=IFERROR(FILTER('All Completed Runs'!C4:C2003,('All Completed Runs'!EC4:EC2003<>"")*('All Completed Runs'!FL4:FL2003<>1)*('All Completed Runs'!D4:D2003="Singapore")*(('All Completed Runs'!AJ4:AJ2003>2019)+(‘All Completed Runs'!AJ4:AJ2003<2023))),””)

Thanks in advance!

Olly.

 

[Oaktree]

Could be dates stored as text (i.e. if you change the formatting of column B to number and the dates still display as dd/mm/yyyy, they are dates stored as text values and not actually Excel dates).

Try using (('All Completed Runs'!AJ4:AJ2003+0)<DATEVALUE("01/01/2020")) instead of ('All Completed Runs'!AJ4:AJ2003<DATEVALUE("01/01/2020")) and similar for 12/31/2013's comparative.

 

[ollyhughes1982]

That didn't seem to work, still the same result. Formulas I have used below:

=IF(A4="","",(IFERROR(UNIQUE(FILTER('All Completed Runs'!E4:E2003,'All Completed Runs'!BN4:BN2003<>"")*('All Completed Runs'!FL4:FL2003<>1)*(('All Completed Runs'!AJ4:AJ2003+0)>DATEVALUE("31/12/2013"))*(('All Completed Runs'!AJ4:AJ2003+0)<DATEVALUE("01/01/2020"))*(('All Completed Runs'!D4:D2003="United Kingdom")+('All Completed Runs'!D4:D2003="Australia")+('All Completed Runs'!D4:D2003="Austria")+('All Completed Runs'!D4:D2003="France")+('All Completed Runs'!D4:D2003="Ireland")+('All Completed Runs'!D4:D2003="Italy")+('All Completed Runs'!D4:D2003="New Zealand"))),"")))

The source column AJ is formatted as 'General' and uses the following formula to calculate:

=IF(E4<>"",YEAR(E4),"")

E is the original date column and is formatted as 'Date' and '14/03/2012' format.

 

[ollyhughes1982]

I can share the file if easier - it's quite large and takes a few minutes to open, but have saved on the correct worksheet and cell for you.

The file is here: parkrun - My parkrun Record (Oliver Hughes - A132324) WORKING 2.xlsx

Thanks for your help so far! Very much appreciated!

 

[ollyhughes1982]

I'm totally stuck on this one!

 

[ollyhughes1982]

I have now resolved this. I had a bit of a mistake in the original date formula. Fixed as per below:

=IF(A4="","",(IFERROR(UNIQUE(FILTER('All Completed Runs'!E4:E2003,('All Completed Runs'!BN4:BN2003<>"")*('All Completed Runs'!FL4:FL2003<>1)*(('All Completed Runs'!D4:D2003="United Kingdom")+('All Completed Runs'!D4:D2003="Australia")+('All Completed Runs'!D4:D2003="Austria")+('All Completed Runs'!D4:D2003="France")+('All Completed Runs'!D4:D2003="Ireland")+('All Completed Runs'!D4:D2003="Italy")+('All Completed Runs'!D4:D2003="New Zealand")))),"")))