Explanation
In this example, the goal is to filter data based on multiple criteria with the FILTER function. Specifically, we want to select data where (1) the group = “A” and (2) the Score is greater than 80. At first glance, it’s not obvious how to do this with the FILTER function. Unlike older functions like COUNTIFS or SUMIFS , which provide multiple arguments for entering multiple conditions, the FILTER function only provides a single argument called “include” to filter data. The trick is to create logical expressions that use Boolean algebra to target the data of interest and supply these expressions as the include argument.
FILTER function
The FILTER function “filters” a range of data based on supplied criteria and extracts matching records. The generic syntax for FILTER looks like this:
FILTER(array,include,[if_empty])
The idea with FILTER is that the include argument is provided as a logical expression that targets data of interest. Most of the challenge in using FILTER is developing a logical expression that correctly targets data that meets all conditions.
Video: Basic FILTER function example
FILTER with multiple criteria
In this problem, we need to configure FILTER to apply two criteria: (1) Group is “A” and (2) Score is greater than 80. To start off, we provide all data (B5:D16) for the array argument, because we want FILTER to return all three columns:
=FILTER(B5:D16,
Next, we need to supply the logic needed to target records where the Group is “A”. We do this with a simple logical expression:
=C5:C16="A"
Here, we compare every value in the Group column to “A”. Because we have 12 rows of data, the result is an array that contains 12 TRUE and FALSE values like this:
{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}
Notice the TRUE values in the array correspond to rows where the Group is “A”. If we were to use this expression by itself as the include argument, FILTER will return all 4 rows where the Group is “A”. Next, we need to add logic to target records with the second condition: Score is greater than 80. This can be done with another simple logical expression:
=D5:D16>80
Like the previous expression, this snippet will return an array that contains 12 TRUE and FALSE values:
{TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}
In this array, TRUE values indicate rows where the score is greater than 80. At this point, we have two simple logical expressions, and we need to join them inside the include argument. To do this, we need to add parentheses around both expressions (to control the order of operations ) and then multiply to two expressions together:
=(C5:C16="A")*(D5:D16>80)
We use multiplication (*) because, under the rules of Boolean algebra , multiplication corresponds to AND logic , and addition (+) corresponds to OR logic .
Video: Boolean algebra in Excel formulas
After each expression is evaluated, we have the following two arrays :
={TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}*{TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}
The math operation of multiplication converts the TRUE and FALSE values to 1s and 0s:
={1;0;0;1;0;0;1;0;0;1;0;0}*{1;1;0;1;1;1;1;0;1;0;1;0}
After multiplication, we have a single array:
={1;0;0;1;0;0;1;0;0;0;0;0}
Notice that the 1s in this array correspond to rows in the data where the Group is “A” and the score is greater than 80. This is exactly what we need to retrieve data that matches both conditions. This array is returned directly to FILTER as the include argument:
=FILTER(B5:D16,{1;0;0;1;0;0;1;0;0;0;0;0},"No data")
The final result from FILTER is the three rows in the data that meet the criteria.
Explanation
In this example, criteria are entered in the range F5:H6. The logic of the formula is:
item is (tshirt OR hoodie) AND color is (red OR blue) AND city is (denver OR seattle)
The filtering logic of this formula (the include argument) is applied with the ISNUMBER and MATCH functions, together with boolean logic applied in an array operation.
MATCH is configured “backwards”, with lookup_values coming from the data, and criteria used for the lookup_array . For example, the first condition is that items must be either a Tshirt or Hoodie. To apply this condition, MATCH is set up like this:
MATCH(items,F5:F6,0) // check for tshirt or hoodie
Because there are 12 values in the data, the result is an array with 12 values like this:
{1;#N/A;#N/A;2;#N/A;2;2;#N/A;1;#N/A;2;1}
This array contains either #N/A errors (no match) or numbers (match). Notice numbers correspond to items that are either Tshirt or Hoodie. To convert this array into TRUE and FALSE values, the MATCH function is wrapped in the ISNUMBER function:
ISNUMBER(MATCH(items,F5:F6,0))
which yields an array like this:
{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE}
In this array, the TRUE values correspond to tshirt or hoodie.
The full formula contains three expressions like the above used for the include argument of the FILTER function:
ISNUMBER(MATCH(items,F5:F6,0))* // tshirt or hoodie
ISNUMBER(MATCH(colors,G5:G6,0))* // red or blue
ISNUMBER(MATCH(cities,H5:H6,0))) // denver or seattle
After MATCH and ISNUMBER are evaluated, we have three arrays containing TRUE and FALSE values. The math operation of multiplying these arrays together coerces the TRUE and FALSE values to 1s and 0s, so we can visualize the arrays at this point like this:
{1;0;0;1;0;1;1;0;1;0;1;1}*
{1;0;1;1;0;1;0;0;0;0;0;1}*
{1;0;1;0;0;1;0;1;1;0;0;1}
The result, following the rules of boolean arithmetic, is a single array:
{1;0;0;0;0;1;0;0;0;0;0;1}
which becomes the include argument in the FILTER function:
=FILTER(B5:D16,{1;0;0;0;0;1;0;0;0;0;0;1})
The final result is the three rows of data shown in F9:H11
With hard-coded values
Although the formula in the example uses criteria entered directly on the worksheet, criteria can be hard-coded as array constants instead like this:
=FILTER(B5:D16,
ISNUMBER(MATCH(items,{"Tshirt";"Hoodie"},0))*
ISNUMBER(MATCH(colors,{"Red";"Blue"},0))*
ISNUMBER(MATCH(cities,{"Denver";"Seattle"},0)))
