Filter this or that

To filter data to include only records where a value is this or that, you can use the FILTER function and simple boolean logic expressions. In the example shown, the formula in F5 is:

=FILTER(B5:D14,(D5:D14="red")+(D5:D14="blue"),"No results")

The result returned by FILTER includes only rows where group is "red" or "blue".

How this formula works

This formula relies on the FILTER function to retrieve data based on a logical test built with simple expressions and boolean logic:

(D5:D14="red")+(D5:D14="blue")

After each expression is evaluated, we have the following arrays:

({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE})+
({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE})

The math operation (addition) coerces TRUE and FALSE to 1 and zero, and the result is a single array:

=({1;0;0;0;0;0;1;0;0;0})+({0;1;0;0;1;0;0;1;0;0})

Since we are using Boolean algebra, there are only two possible values operation: 1 or 0. Thus, the final array contains only 1's and 0's:

={1;1;0;0;1;0;1;1;0;0}

This final array is delivered to the FILTER function as the "include" argument, and FILTER returns only rows that correspond to a 1.