The FILTER function is a useful but relatively recent addition to Microsoft Excel. FILTER is one of the new Dynamic Array Functions, which supports accepting arguments that are dynamic arrays or spilled array references using the new '#' operator (e.g., "A1#" to reference the dynamic array in cell A1).
Learning to use FILTER will significantly enhance your Excel formula calculations for many common use cases and make life much easier.
You can find the examples in this blog for FILTER in this Excel workbook.
Filter Syntax
| FILTER (array, include, [if empty]) | |
| array | The array or reference to filter. |
| include | The filter condition – see examples below |
| [if empty] | Optional value to return when the filter result is empty. |
The FILTER function takes a range or array and returns a filtered array using the specified "include" criteria. The first argument, "array", sets the range or array to filter, the second argument, "include", specifies the "include" criteria, and the optional third argument, "if empty", sets the value to return when the filter result is empty.
When the "if empty" argument is not specified, and the filter result is empty, the function returns a "#CALC!" error value.
To illustrate the FILTER function in action, here is a sample table named "FilterData":
Figure 1: Sample FilterData Table
To specify the "include" criteria, you must select a range or array of the same length as the filter data with the = comparison operator and value to filter by. If the filter data is a date or numeric type, you can also use <, <=, >, and >= operators.
For example, to filter the above table by Product Line to show only the Spread.NET items, use this formula:
=FILTER(FilterData,FilterData[Product Line]="Spread.NET")
Figure 2 Example 1
Note that the above result in B27:F33 is formatted to appear like a table but is not a table, as dynamic array formulas and spilling behavior are not supported in table cells. Instead, the range is formatted to appear like a table, and the table header cells B26:F26 are merely copies of the original table header cells.
The Total in row 34 is also added manually, with a formula in cell F34 to sum the cells above (you can insert such a formula using the Alt+'=' keyboard shortcut).
The result in cell B27 can be filtered again using the spilled range reference operator '#' to reference the formula cell and perform another FILTER.
This example shows using the explicit intersection to specify the cell range for the criteria by using the column reference C:C and the range intersection operator (the space character) with the formula cell reference and the spilled range operator:
=FILTER(B27#,B27# C:C="NewLicense")
Figure 3 Example 2
The expression B27# C:C in the formula for cell H27 above uses the range intersection operator (the space character) to perform an explicit intersection between the cell range B27#, which is the spilled range B27:F33 for the formula in B27.
The column range C:C, which is all cells in column C, results in the range C27:C33, the cells in the "Product" column.
If the filter criteria instead specified "Deployment License" for the Product, and nothing is specified for the "is empty" argument, then the filter result would be a "#CALC!" error and look like this:
=FILTER(B27#,B27# C:C="DeploymentLicense")
Figure 4 Example 3
When the "is empty" argument is specified, that value is returned instead of the "#CALC!" error:
=FILTER(B27#,B27# C:C="DeploymentLicense","NONE FOUND")
Figure 5 Example 4
Complex Filter Conditions
More complex filter conditions can be specified by using the * and + operators to combine multiple filter criteria enclosed in parentheses.
The * operator can specify "AND" between two filter criteria, and the + operator can set "OR" – allowing for two or more criteria to be combined in one FILTER function call.
This example filters by two criteria and uses the * operator to specify "AND" – that both conditions must be satisfied:
=FILTER(FilterData,(FilterData[Product Line]="SpreadJS")*(FilterData[Product]="DeploymentLicense"))
Figure 6 Example 5
You can form more complex filter criteria using multiple conditions together with both * and +, grouping the conditions and sets of conditions inside parentheses.
FILTER in GrapeCity Products
The FILTER function is supported in these GrapeCity spreadsheet controls and components:
- Spread.NET Windows Forms Spreadsheet Control | Demo
- SpreadJS Javascript/Typescript/Angular/React/VueSpreadsheet Widget | Demo
- GrapeCity Documents for Excel .NET and Java Controls | .NET Demo | Java Demo
Sean Lawyer