Filtering Tables: In a single function

This content is licensed under The MIT License. See here for more details.

The FILTER function lets us filter a table on some criteria, which is great:

=FILTER(Table, Criteria)

For example;

=FILTER(
    Table,
    LEN(VALUETOTEXT(Table[Row1])) <> 0
)

gives us all entries in Table that have a non-empty Row. But what if we want to filter on more? Well, because Excel is nice enough to coerce binary arrays (e.g., {TRUE, TRUE, FALSE}) into numbers (e.g., {1, 1, 0}) when we try to do math with them, we can use the BITAND and BITOR functions to "smush" criteria (to be specific, they are each calculated individually, and the results are combined via boolean logic).

For example,

=FILTER(
    Table,
    BITAND(
        LEN(VALUETOTEXT(Table[Row1])) <> 0,
        LEN(Table[Row2]) <> 0
    )
)

gives us the same, but also filtered on entries with a populated Row2. Unfortunately - unlike the AND and OR operators - the BITAND and BITOR functions only accept two arguments, but we can get around that by nesting them:

=FILTER(
    Table,
    BITAND(
        LEN(VALUETOTEXT(Table[Row1])) <> 0,
        BITOR(
            LEN(Table[Row2]) <> 0,
            BITOR(
                LEN(Table[Row3]) <> 0,
                LEN(Table[Row4]) <> 0
            )
        )
    )
)

This gives us a fully filtered array, which spills into neighboring cells. What’s great about this (vs. just referencing the table and getting the whole thing), is that a spilled value is virtual. We can perform additional operations on it, and if they return a single cell result, we get that single cell:

=ROWS(
    FILTER(
        Table,
        BITAND(
            LEN(VALUETOTEXT(Table[Row1])) <> 0,
            BITOR(
                LEN(Table[Row2]) <> 0,
                BITOR(
                    LEN(Table[Row3]) <> 0,
                    LEN(Table[Row4]) <> 0
                )
            )
        )
    )
)

…which just gives us a count of how many items match our criteria.