Google Sheets – Combine OR and AND in One Filter Formula

formulasgoogle sheetsgoogle-sheets-query

=filter('CASES LIST'!C:S,'CASES LIST'!Q:Q="admission",len('CASES LIST'!S:S))

I have in the sheet column Q – has "admission", "consultation", other items in dropdown list.

In column S- other dropdown list as well.

In column R- drop down list

I want to filter data based on either of these two criteria:

1- Column Q="admission" AND column S is not empty

2- OR Column Q= "consultation"

If either of these criteria is met, the row C:S, will be copied to the desired sheet

I made this formula, but it doesn't seem working

=filter('CASES LIST'!C:S,'CASES LIST'!Q:Q="admission",len('CASES LIST'!S:S))+('CASES LIST'!Q:Q="consultation")

Best Answer

Please use the following query formula (adjusting ranges to your needs), instead of the filter you already use.

=QUERY({P1:S},"where Col2='admission' AND Col4<>'' OR Col2='consultation' ",0)

enter image description here

Functions used:

Related Topic