0

How can I use FILTER in a single formula to check for multiple conditions, such as in this case?

The filtered range must be equals1 AND notequal2 AND equals3 OR equals4

This is of course an arbitrary scenario, I just wish to know the syntax of the logic operators.

1

=FILTER(F:F, G:G="equals1",
             H:H<>"notequal2",
            (I:I="equals3") +
            (J:J="equals4"))

  • 1
    Good answer, but it could benefit from some explanation. What does (I:I="equals3")+(J:J="equals4") do - why the parenthesises and the +? Note that the OP asks "I just wish to know the syntax of the logic operators." – Vidar S. Ramdal Feb 14 at 15:39
0

It seems FILTER supports * and + as operators within conditions in the syntax FILTER(range, condition1, [condition2, ...]) (AND between conditions is implied). Thus, in your example you could use (I assumed you meant AND precedence over OR; other options can be easily handled)

FILTER(range, (range=equals1)*(range<>equal2)*(range=equals3)+(range=equals4))

Notes:

  1. The use I have provided is the most versatile, as it allows for an arbitrary nesting and precedence of AND/OR operations.

  2. In the "standard" syntax use there is no option for OR operations.

  3. A "standard" syntax use FILTER(range, condition1, [condition2, ...]) can be replaced by FILTER(range, (condition1)*(condition2)*(...)).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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