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

1 Answer 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)

enter image description here

Functions used:

  • 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. Commented Jan 6, 2021 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.