This tutorial explains how to use a slicer to filter data in a PivotBy report in Microsoft Excel.
Understanding the Problem
- Excel functions like COUNTA, SUM, MIN, MAX, and AVERAGE ignore filters applied through slicers.
- This poses a challenge when you want to use a slicer to dynamically filter the data used in a PivotBy report.
The Solution: Combining SUBTOTAL, BYROW, and LAMBDA
The solution involves creating a logical test within the PivotBy function’s “filter_array” argument. This test determines which rows are included or excluded based on the slicer selection. Here’s a breakdown of the process:
- Using SUBTOTAL and COUNTA:
- The SUBTOTAL function, unlike COUNTA alone, is sensitive to filters applied through slicers.
- Using SUBTOTAL with function number 3 (COUNTA) allows you to count the number of values in a range while respecting the slicer filter.
- Incorporating BYROW:
- The BYROW function enables you to perform calculations on each individual row of a range.
- By nesting SUBTOTAL(3, range) within BYROW, you can count values on a row-by-row basis, respecting the slicer filter for each row.
- Utilizing LAMBDA:
- While SUBTOTAL doesn’t directly appear as an option within BYROW, you can overcome this by using the LAMBDA function.
- LAMBDA lets you define a custom function within BYROW. In this case, you would define a function that utilizes SUBTOTAL to perform the count on each row.
Constructing the Formula
The formula within the “filter array” argument of the PivotBy function would look something like this:
=BYROW(Transactions[Product_Group], LAMBDA(TR, SUBTOTAL(3, TR)))
Where:
- [Product_Group]: This represents the range containing the data you want to filter using the slicer.
- TR: This is a parameter within the LAMBDA function, acting as a reference to each individual row within the [Product_Group] column.
- SUBTOTAL(3, TR): This performs a COUNTA on each row (TR) while respecting the slicer filter.
Outcome
By using this combination of functions, the PivotBy report becomes responsive to the filters applied through the slicer. The formula returns 1 for rows meeting the slicer criteria and 0 for rows that don’t. This allows the PivotBy report to dynamically update based on the slicer selection.