Filtering Tables: In a single function
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.