Filters an array using a logical array. FILTER ({ 1, 2, 3 }, { FALSE, TRUE, TRUE }) FILTER ({ 1; 2; 3 }; { FALSE; TRUE; TRUE }) returns { 2, 3 }{ 2; 3 }. 1 is not part of the returned array, as the corresponding element in the second array is FALSE. 2 and 3 are both part of the returned array, as the corresponding elements in the second array are both TRUE.

In other words, the returned array contains an element found in the first array only if the corresponding element in the second array is TRUE. The two arrays must be equal in size.

Filtering based on logical operations

The second array is typically not provided explicitly. Rather, an operation is used that returns a logical array.

This formula returns only those values which are greater than 1:

Filtering students based on their grades

The second array does not need to reference the same array as the first array. Consider this formula:

The first array of the formula above contains student names and the second array contains their test scores. The formula returns an array of names of the students who scored more than 85: { "Sally", "Luke" }{ "Sally"; "Luke" }.

Counting the filtered elements

Use the SIZE function to determine the number of elements returned by FILTER. This formula returns the number of values which are greater than 1:

When no values match

If no values match, FILTER returns an empty array. (Spreadsheets typically return an error instead.) An empty array is not an error, so IFERROR cannot be used to detect that no values match.

To return other values when no values match, give them as the third parameter. This formula returns {  - 50 }{  - 50 }, as no values are greater than 50:

Combining multiple conditions

Use the logical operators || to express "logical or" (disjunction) and && to express "logical and" (conjunction). This formula returns values which are greater than 1 or are even numbers:

This formula returns only those values which are both greater than 1 and are odd numbers:

Traditionally, spreadsheets use the + operator to express "or" and the * operator to express "and." These operators only accept numbers, not logical values, so use || and && instead. (The functions OR and AND cannot be used in this context, as they return a single logical value, not a logical array.)

Cutting down on repetition with LET

FILTER often requires an array of elements to be given twice: as the first parameter, which provides the array to filter, and as the second parameter, which provides the logical array determining which elements to return.

In the examples above, the array { 1, 2, 3 }{ 1; 2; 3 } is used for demonstration purposes. However, formulas often need to devote much more space to specifying the array elements, which makes the repetition more of an issue.

Formulas with long, repeated sections are often harder to read and run more slowly than those without repetition. Moreover, when parts of a formula are repeated, errors can creep in when the formula is updated if only some of those repeated parts are updated.

The LET function solves this issue, by allowing values used in a single formula to be assigned names. Consider this formula, where the values to filter are repeated twice:

With LET, := is used to assign the name Values to the array of values, which makes the actual FILTER invocation much simpler:

Had the definition of Values been more verbose, the benefits brought by LET would have been even greater.

Using a formula fragment to filter elements

Instead of passing a logical array as the second parameter, you can also use a formula fragment which is expected to return TRUE if the element should be included and FALSE otherwise. These formulas are equivalent:

The formula fragment you supply as the second parameter is run once per element. The element can be accessed as the Element value. Moreover, the position of the element (1 for the first element, 2 for the second element, etc) can be accessed as the Index value and the source array — given as the first parameter to FILTER — can be accessed as the Source value.

Use this formula to only return the last three elements of an array:

Using a formula fragment is useful if you need to compare array elements only against themselves. It has the benefit of saving you from having to repeat the source array in the formula. That particular benefit can also be had using the LET function (see above). Use whatever style suits you best.

Using a formula fragment to filter an array is not commonly supported by spreadsheets.

Using FILTER instead of the *IF functions

When combined with other functions, FILTER can be used as a replacement for functions like AVERAGEIF, COUNTIF and SUMIF. These functions all boil down to filtering an array before applying an additional operation.

SUMIF filters an array based on a condition and then adds the resulting numbers together, returning the result. These formulas are equivalent and return 1100, because only 100 and 1000 are greater than 25:

COUNTIF filters an array based on a condition and then returns the number of matching elements. These formulas are equivalent and return 2, because exactly two array elements are greater than 25:

The FILTER invocation in the second formula above returns { 100, 1000 }{ 100; 1000 }. The SIZE invocation then returns the size of that array, 2.

AVERAGEIF filters an array based on a condition and then returns the average of the elements matching the condition. These formulas are equivalent and return 550, because that is the average value between 100 and 1000:

When given a third parameter, AVERAGEIF, COUNTIF and SUMIF apply the condition to the first array, while applying the operation (averaging, counting and summing) to the third array.

These equivalent formulas add together 40 and 50, but not 30, as these numbers are associated with the text string "Sally":

Using FILTER instead of the *IFS functions

AVERAGEIF, COUNTIF and SUMIF also come in versions designed to be used with multiple conditions: AVERAGEIFS, COUNTIFS and SUMIFS. There are also two *IFS functions with no *IF counterpart: MINIFS and MAXIFS, which return the smallest and largest number, respectively, from an array, provided that a number of conditions are met.

Consider this AVERAGEIFS formula:

The formula above returns an average of the test scores obtained by female students who reside in Arkansas (abbreviated "AK"). Here, only one test score matches (82) and is returned, because while two female students appear in the data, only one of the students resides in Arkansas.

Here is the equivalent formula using FILTER:

The FILTER version is far more versatile. AVERAGEIFS can only be used with "logical and" and not "logical or" (or any other combination of logical operations). What if we want the average of all test scores associated either with female students or students residing in Arkansas? AVERAGEIFS does not provide this feature, but using FILTER, it's as easy as turning && into ||:

FILTER, MAP and REDUCE

FILTER, MAP and REDUCE are commonly used together in a data processing pipeline to transform data. FILTER removes irrelevant elements from an array and MAP transforms the remaining elements. REDUCE, finally, transforms the resulting array to a single value.

Instead of REDUCE, simpler functions can be used to reduce an array of values to a single value. Those include SUM, which adds all array elements together and returns the result, and AVERAGE, which calculates an average of all elements.

This formula calculates the total payroll for employees who worked more than 30 hours in a given time period:

The formula assumes the existence of an Employees table with the following columns:

• Employees!NameEmployees!Name, an array of employee names.
• Employees!HoursWorkedEmployees!HoursWorked, an array representing the number of hours each employee worked.
• Employees!HourlyRateEmployees!HourlyRate, an array containing each employee's hourly rate.

This formula fragment returns an array containing the names of the employees who worked for more than 30 hours:

The filtered array of names is then passed to MAP, which returns an array consisting of the payroll costs of all employees who worked for more than 30 hours:

In this step, MAP transforms each employee name into their total payroll cost using XLOOKUP. First, it is used to look up the number of hours the employee worked and then to fetch the employee's hourly rate.

Finally, SUM is provided the array of payroll costs and returns the total payroll.

Chaining multiple FILTER and MAP invocations

A single formula can use multiple invocations of FILTER and MAP. For instance, an innermost FILTER invocation can filter the raw array once, and then hand this data to MAP, which transforms the filtered data. This data can then, once more, be given to FILTER, which filters out additional elements, and so on.

Consider this formula:

The formula above filters the number array { 326.60, 402.80, 290.00, 128, 3002 }{ 326,60; 402,80; 290,00; 128; 3002 }, which lists prices, leaving only those below 1000. It then applies a 10% markup to each price, keeps only those exceeding 200 and finally returns the sum of these prices.

Working our way outwards from the number array, FILTER is applied to it and uses the formula fragment Element < 1000Element < 1000 to exclude prices of 1000 or more. That leaves { 326.60, 402.80, 290.00, 128 }{ 326,60; 402,80; 290,00; 128 }.

MAP is applied to this array, with the formula fragment Element * 1.1Element * 1,1, which applies a 10% markup to each price: { 359.26, 443.08, 319.00, 140.8 }{ 359,26; 443,08; 319,00; 140,8 }.

FILTER is then applied to this array using this formula fragment, Element > 200Element > 200, which filters out all elements which are not greater than 200. That leaves the array { 359.26, 443.08, 319.00 }{ 359,26; 443,08; 319,00 }. Finally, SUM is applied to this array, returning the grand total 1121.34.