Excel FILTER Function Summary Reports

These Excel FILTER function summary report examples show how to create multi-column reports, that update automatically, when the source data changes. Step-by-step 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 Function

The 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 multi-column summary reports from a source data set, based on criteria.

FILTER Function Video

Before 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 Syntax

The 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 semi-colon separator might be required.

1. array - select the range of cells that you want in the results
2. include - enter one or more criteria settings
3. if_empty - (optional) enter a value to show if there are no results (result array of values is empty)

Food Sales Data

For 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:

• Date, Region, City, Category, Product, Cases, Cost, Yr
• Yr column has formula: =YEAR([@Date])

Food Sales Named Ranges

Before 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:

• ColDate, ColReg, ColCity, ColCat, ColProd, ColCase, ColCost, ColYear

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.

• For example, to refer to the Region column, formulas will use ColReg, instead of Sales_Data[Region]

Create Named Ranges for Columns

To name the data cells in a table column, follow these steps:

• Click at the top of a column heading cell, to select all the data in that column
• The headings cell should not be selected
• Click in the Name Box, to the left of the Formula Bar
• Type a one-word name for the selected cells, e.g. ColDate
• Press the Enter key, to complete the name (If you skip this step, the name will NOT be created)

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:

• ColReg, ColCity, ColCat, ColProd, ColCase, ColCost, ColYear

See Defined Names

After 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:

• On the Excel Ribbon, go to the Formulas tab
• In the Defined Names group, click the Name Manager command

The Name Manager opens, with a list of all the workbook and worksheet names.

• In the list, click on the name that you want to see details for
• At the bottom, the Refers To box shows the location of that named range, or a formula, if the name is not a range of cells
• Click in the Refers to box, and the worksheet range should be highlighted, with a scrolling marquee (marching ants) around its border

FILTER Summary Report - 2 Columns

This FILTER function example creates a dynamic summary report, with 2 columns:

• Names from the Category column
• Total amount from the Cost column

The results are based on the following 2 criteria:

• Region name entered in cell B4
• Year number entered in cell B7

FILTER Report Formula

Here is the summary report formula from cell C4.

• =LET(CC, SORT(UNIQUE(FILTER(ColCat, (ColReg=A4) * (ColYear=A7)))), CHOOSE({1,2},CC, SUMIFS(ColCost,ColReg,A4, ColCat,CC, ColYear,A7)))

You can copy that formula, and paste it into the workbook, and the results will show the 2-column 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, step-by-step

How It Works - 2 Column Report

In this section, you'll see how each part of the 2-column 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 List

In the formula, the FILTER function creates a one-column list of food categories, based on two criteria.

• FILTER(ColCat,(ColReg=A4)*(ColYear=A7))

This FILTER function uses 2 arguments::

1. array - results will come from Category column (ColCat)
2. include - category names are included for records where:
   • Order record Region (ColReg) matches name in cell A4
   • Order record Year (ColYear matches number in cell A7

Unique Sorted List

Next, 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.

• SORT(UNIQUE(FILTER(ColCat,(ColReg=A4)*(ColYear=A7))))

2) Set Variable for Category List

In the formula, the filtered category list is needed twice:

1. In first column of results, to show category names
2. In SUMIFS function, to calculate total for each category

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 Function

The LET function syntax has the following arguments:

• name1: the first variable name for the formula - must start with a letter
• name_value1: value assigned to name1
• calculation_or_name2: Add more pairs of names and values (optional),
  • OR enter a calculation that uses the assigned names

NOTE: The final argument MUST be a calculation

Notes

• The LET function is available in Excel 365, and not in earlier versions.
• Using the LET function can make complex formulas shorter, faster, and easier to read.
• Read more about the LET function on the Microsoft website.

LET in FILTER Report Formula

Here's the start of the FILTER report formula, with LET at the beginning

• =LET(CC, SORT(UNIQUE(FILTER(ColCat, (ColReg=A4)* (ColYear=A7))))

Here are the LET arguments used in the full formula:

1. name1: CC
2. name_value1: SORT(UNIQUE(FILTER(ColCat, (ColReg=A4)* (ColYear=A7))))
3. calculation: CHOOSE formula, explained in next section, where CC name is used twice:

CHOOSE({1,2},CC, SUMIFS(ColCost, ColReg, A4, ColCat,CC, ColYr, A7))

3) Calculate Category Totals

Next, the formula calculates the total cost for each filtered food category(CC), in the specified region and year

• SUMIFS(ColCost,ColReg, A4, ColCat,CC, ColYr, A7)

In this part of the formula:

• Amount to sum comes from Cost column (ColCost)
• Order record Cost is included if:
  • Order record Category (ColCat) is in the filtered list (CC)
  • Order record Region (ColReg) matches name in cell A4
  • Order record Year (ColYear) matches number in cell A7

FILTER Summary Report - 5 Columns

In 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:

• Category, Total amount, Average amount, Count of orders, Maximum amount

All of the results are based on the criteria entered for Region (A4) and Year (A7).

FILTER Formula for 5-Column Report

Here 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.

• =LET(CC, SORT(UNIQUE(FILTER(ColCat, (ColReg=A4)* (ColYear=A7)))),
     CHOOSE({1,2,3,4,5},CC,
          SUMIFS(ColCost,ColCat,CC,ColReg,A4, ColYear,A7),
          ROUND(AVERAGEIFS(ColCost,ColCat,CC,ColReg,A4, ColYear,A7),0),
          COUNTIFS(ColCat,CC,ColReg,A4, ColYear,A7),
          MAXIFS(ColCost,ColCat,CC,ColReg,A4, ColYear,A7)))

How It Works - 5 Column Report

In this section, you'll see how each part of the 2-column 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 Totals

This 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:

•  CHOOSE({1,2,3,4,5}

Next, the formula has a calculation for each column

1) Category List

The first column is the category list, created by the variable - CC

2) Total Sales

The 2nd column is the total sales for each category. It uses the same SUMIFS formula as the 2-column report:

•  SUMIFS(ColCost,ColCat,CC,ColReg,A4, ColYear,A7)

3) Average Sale

The 3rd column is the average sale amount for each category. It uses the AVERAGEIFS function, and the ROUND function, with zero decimal places:

• ROUND(AVERAGEIFS(ColCost,ColCat,CC,ColReg,A4, ColYear,A7),0),

4) Count of Sales

The 4th column is the count of sales for each category. It uses the COUNTIFS function:

• COUNTIFS(ColCat,CC,ColReg,A4, ColYear,A7)

5) Highest Sale (Max)

The 5th column is the highest sale amount for each category. It uses the MAXIFS function:

• MAXIFS(ColCost,ColCat,CC,ColReg,A4, ColYear,A7)

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 Examples

To see more examples of using the FILTER function, go to the following pages, here on the Contextures site:

Hide Used Employee Names

After 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 List

Select 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 File

FILTER Reports: To see the 2-column and 5-column FILTER function report examples, download the FILTER function report examples workbook. The file is zipped, and is in Excel xlsx format, with no macros.