“Can you use a Slicer with the GROUPBY function?” seems to be a common question in the Excel world right now. I think because GROUPBY is new and there’s the perception that it is a replacement for Pivot Tables (which it’s not), the question of whether it supports Slicers seems to be an obvious question to ask.
The Slicer cannot be connected directly to the GROUPBY function. It needs to be attached to the source data (which must be set up as a Table) that the GROUPBY function is summarising. In my demo the Table, called Sales, is in A2:G31.
Subtotal Function
The first step is to add an extra column to the Table. This column will be used in the Filter argument of the GROUPBY function. I named this column Filter although you can use any name.
After typing the word Filter into H2, I typed the following formula into H3 (the cell below the column heading): =SUBTOTAL(103,A3)
103 represents the COUNTA function that ignores hidden/filtered values.
A3 is the range, or in this case single cell that will be counted. I could have selected any single cell on row 3 as long as it would never be empty.
The formula is automatically copied down the column (because the column is part of the Table) and each formula references a cell in column A on that row.
Once the filter has been applied to the table via the Slicer, if the row is visible, SUBTOTAL returns 1 but if it’s hidden it returns 0.
GROUPBY
I’m assuming you are already familiar with the GROUPBY function (I have covered it in other videos and tutorials). The key element is the filter-array argument
=GROUPBY(Sales[[#All],[Flavour]],Sales[[#All],[Revenue]],SUM,3,,,Sales[[#All],[Filter]]=1)
In the above formula the filter-array argument is the final argument. The function is displaying the Total Revenue for each Flavour with a grand total but only for the rows in the Sales Table where the Filter column in that Table is equal to 1.
Download a copy of the file used in this video: https://share.zight.com/4guGG4YR