These Excel FILTER function summary report examples show how to create multicolumn reports, that update automatically, when the source data changes. Stepbystep instructions, and Excel workbook NOTE: FILTER is one of the Excel spill functions (dynamic arrays) that are available in Microsoft 365 plans. See more on the FILTER Function Examples page. Author: Debra Dalgleish 
Excel FILTER FunctionThe Excel FILTER function creates dynamic filtered lists, based on criteria, that update automatically, when the source data changes. This page has advanced FILTER function examples, that show how to create multicolumn summary reports from a source data set, based on criteria. FILTER Function VideoBefore you try the advanced examples, you can get an introduction to the Microsoft Excel FILTER function in this short video. You'll see three FILTER function examples, and there are written instructions for these examples on the FILTER Function Examples page. 
FILTER Function SyntaxThe FILTER function syntax has 3 arguments  array, include, and if_empty (optional) Note: The comma separator is used in these examples. For other regional settings, a semicolon separator might be required.

Food Sales DataFor the FILTER function summary report examples shown on this page, the data set is stored in a food sales table, named Sales_Data, on the FoodSales sheet. These are food product orders, for an imaginary food company, and you can get this data in the Download sectionat the bottom of this page. There are 8 columns of data in the Sales_Data table, which you can see in the screen shot below:

Food Sales Named RangesBefore setting up the FILTER formulas, several named ranges were set up in the Excel sample workbook. On the FoodSales sheet, a named range was created for each column in the Sales_Data. The named ranges are:
Those names will be used in the FILTER formulas, instead of using structured table references. This will make the formulas shorter, and easier to read.
Create Named Ranges for ColumnsTo name the data cells in a table column, follow these steps:
After the first name is completed, repeat those steps to name the remaining 7 columns in the food sales table. In the sample workbook, the following names are used:

See Defined NamesAfter you've set up all the names for the workbook, you might want to review them, and make sure everything is set up correctly. To see the list of names, and their range on the worksheet, follow these steps:
The Name Manager opens, with a list of all the workbook and worksheet names.

FILTER Summary Report  2 ColumnsThis FILTER function example creates a dynamic summary report, with 2 columns:
The results are based on the following 2 criteria:
FILTER Report FormulaHere is the summary report formula from cell C4.
You can copy that formula, and paste it into the workbook, and the results will show the 2column summary report, like the one in the screen shot below. Note: If you'd like details on how this formula works, go to the next section. It shows how this complex formula was built, stepbystep 
How It Works  2 Column ReportIn this section, you'll see how each part of the 2column FILTER report formula works. There are 3 main steps:  1) Create a Category List  2) Set Variable for Category List  3) Calculate Category Totals 1) Create Category ListIn the formula, the FILTER function creates a onecolumn list of food categories, based on two criteria.
This FILTER function uses 2 arguments::
Unique Sorted ListNext, the UNIQUE function removes any duplicate category names from the FILTER results. After that, the SORT functions puts the names in A to Z order.

2) Set Variable for Category ListIn the formula, the filtered category list is needed twice:
Instead of typing the SORT/UNIQUE/FILTER formula twice, use the LET function, where you can assign a name (variable), then refer to that name within the LET formula. LET FunctionThe LET function syntax has the following arguments:
NOTE: The final argument MUST be a calculation Notes
LET in FILTER Report FormulaHere's the start of the FILTER report formula, with LET at the beginning
Here are the LET arguments used in the full formula:
CHOOSE({1,2},CC, SUMIFS(ColCost, ColReg, A4, ColCat,CC, ColYr, A7)) 
3) Calculate Category TotalsNext, the formula calculates the total cost for each filtered food category(CC), in the specified region and year
In this part of the formula:

FILTER Summary Report  5 ColumnsIn this FILTER report formula, the results spread across 5 columns, and spill down as far as needed. In the screen shot below, there is a formula in cell C4, and the results fill cells C4 to G7. For each category in the filtered list, the report shows:
All of the results are based on the criteria entered for Region (A4) and Year (A7). FILTER Formula for 5Column ReportHere is the formula from cell C4, which is similar to the previous example. However, instead of 2 numbers in the CHOOSE array {1,2}, there are 5 numbers {1,2,3,4,5}, to create 5 columns of results. In this formula, the CC variable set in the first argument for LET. After that, the CC variable is used 5 times in the calculation argument.

How It Works  5 Column ReportIn this section, you'll see how each part of the 2column FILTER report formula works. There are 3 main steps:  1) Create a Category List  same as 2 Column report  see details there  2) Set Variable for Category List  same as 2 Column report  see details there  3) Calculate 4 totals for each Category 3) Calculate Category TotalsThis formula returns a list of categories, and four columns with calculated totals for each category. The CHOOSE function creates the 5 columns, and it starts with an array of 5 numbers:
Next, the formula has a calculation for each column 1) Category ListThe first column is the category list, created by the variable  CC 2) Total SalesThe 2nd column is the total sales for each category. It uses the same SUMIFS formula as the 2column report:
3) Average SaleThe 3rd column is the average sale amount for each category. It uses the AVERAGEIFS function, and the ROUND function, with zero decimal places:
4) Count of SalesThe 4th column is the count of sales for each category. It uses the COUNTIFS function:
5) Highest Sale (Max)The 5th column is the highest sale amount for each category. It uses the MAXIFS function:
The formula results start in cell C4, where the formula is entered, and spread across to column G. The number of rows will change, depending on which region and year are entered as criteria 
More Filter ExamplesTo see more examples of using the FILTER function, go to the following pages, here on the Contextures site: Hide Used Employee NamesAfter an employee name has been used in the "On Call" list, the FILTER function prevents it from appearing in the selection list. Employee List  Dynamic Arrays example Region Employees Drop Down ListSelect a region name from one drop down, and the FILTER function creates a dynamic list of employees from that region. Dependent Drop Down from Dynamic Arrays Get the Sample FileFILTER Reports: To see the2column and 5column FILTER function report examples, download the FILTER function report examples workbook. The file is zipped, and is in Excel xlsx format, with no macros. 
Last updated: May 7, 2022 2:59 PM