How to combine OR , AND in one filter formula in google sheet

Question

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

marikamitsos · Accepted Answer · 2021-01-06 09:23:45Z

1

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)

Functions used:

answered Jan 6, 2021 at 9:23

marikamitsos

12.5k3 gold badges27 silver badges54 bronze badges

1

Have you tried the given solution? Did it work for you? If yes, as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. If not, please read how to and share a test sheet so as you can be easier helped.
– marikamitsos
Jan 6, 2021 at 19:57

Add a comment |

1 Answer 1