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]

No, (+) means OR, (*) means AND.

Just as your other criteria were ANDs, you can append your new ones in the same way.

=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)),"")

 

[ollyhughes1982]

No, (+) means OR, (*) means AND.

Just as your other criteria were ANDs, you can append your new ones in the same way.

=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)),"")

Ah yes, of course. I need to do it the other way around as well, as want to find the ones outside those three years. Thanks

 

[ollyhughes1982]

No, (+) means OR, (*) means AND.

Just as your other criteria were ANDs, you can append your new ones in the same way.

=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)),"")

Doesn’t have and under a year and over a year cancel each other out though? I was thinking I had to have these bracketed off

 

[Oaktree]

Think of it as a series of TRUE (1) and FALSE (0) values multiplied against each other.

2019 would be FALSE for > 2019, and TRUE for < 2023, so 0 * 1 = 0, the record is excluded
2020 would be TRUE for > 2019, and TRUE for < 2023, so 1 * 1 = 1, the record is included
2023 would be TRUE for > 2019, and FALSE for < 2023, so 1 * 0 = 0, the record is excluded

 

[ollyhughes1982]

Think of it as a series of TRUE (1) and FALSE (0) values multiplied against each other.

2019 would be FALSE for > 2019, and TRUE for < 2023, so 0 * 1 = 0, the record is excluded
2020 would be TRUE for > 2019, and TRUE for < 2023, so 1 * 1 = 1, the record is included
2023 would be TRUE for > 2019, and FALSE for < 2023, so 1 * 0 = 0, the record is excluded

Great, thank you

 

[ollyhughes1982]

I have a similar, slightly more complicated one where I am trying to do a similar thing. What you told you me works for the 1st column, but it doesn’t work for the 2nd column, for some reason. Does the fact that the 2nd column is returning a date affect it? I just get one cell showing “00/01/1900”. It should show a corresponding date for each one.

1st column

Original:

=IFERROR(FILTER('All Completed Runs'!C4:C2003,('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"))),"")

New:

=IFERROR(FILTER('All Completed Runs'!C4:C2003,('All Completed Runs'!BN4:BN2003<>"")*('All Completed Runs'!FL4:FL2003<>1)*('All Completed Runs'!AJ4:AJ2003>2013)*('All Completed Runs'!AJ4:AJ2003<2021)*(('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"))),"")

2nd column

Original:

=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"))),"")))

New:

=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>2013)*('All Completed Runs'!AJ4:AJ2003<2021)*(('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"))),"")))

Below is the layout and what the entries were before any changes:

Thanks again.

 

[Oaktree]

Does the fact that the 2nd column is returning a date affect it?

Yes. In your first example, you had a field that contained just the year, so we could use the comparison of e.g. > 2013.

Since your new example has actual dates in the column, you can't compare > 2013, you need to compare > DATEVALUE("31/12/2013") and < DATEVALUE("1/1/2021") instead.

Reason being: Excel treats dates as # of days after 1/1/1900. If you were to enter a date of 31/12/2013 in a cell and format that cell as a number, it would show the value of 41,639. You'd be comparing *that* value against 2013, not the year of the date as you were intending to.

 

[ollyhughes1982]

Yes. In your first example, you had a field that contained just the year, so we could use the comparison of e.g. > 2013.

Since your new example has actual dates in the column, you can't compare > 2013, you need to compare > DATEVALUE("31/12/2013") and < DATEVALUE("1/1/2021") instead.

Reason being: Excel treats dates as # of days after 1/1/1900. If you were to enter a date of 31/12/2013 in a cell and format that cell as a number, it would show the value of 41,639. You'd be comparing *that* value against 2013, not the year of the date as you were intending to.

Brilliant, thank you very much. I will try it now and come back if I get stuck. Thanks again for your advice

 

[ollyhughes1982]

Yes. In your first example, you had a field that contained just the year, so we could use the comparison of e.g. > 2013.

Since your new example has actual dates in the column, you can't compare > 2013, you need to compare > DATEVALUE("31/12/2013") and < DATEVALUE("1/1/2021") instead.

Reason being: Excel treats dates as # of days after 1/1/1900. If you were to enter a date of 31/12/2013 in a cell and format that cell as a number, it would show the value of 41,639. You'd be comparing *that* value against 2013, not the year of the date as you were intending to.

I have tried this:

=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>DATEVALUE("31/12/2013"))*('All Completed Runs'!AJ4:AJ2003<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"))),"")))

but still seem to be getting the same result. Have incorporated the DATVALUE formulas correctly?