You could set it up so that enter a value in the cell filters the table and not the other way round.
Here I have defined two named cells in
H2 and called them
p is for "parameter". You can of course call these whatever you want, or not name them at all, but I think this is more easily understood.
The full data table is in cells
A1:E6. I have formatted this as a Table so that any references to it in other formulas will grow if more data are added to the table. This table is called
full_data. You might consider having this on another hidden sheet, or hide the columns containing the full data.
G5 I have this formula to get the headers:
G6 I have this formula to get the filtered data:
Let's look at this more closely:
Let the name
qcrit1 (query by criteria 1) be the result of the
full_data where the
full_data[Crit1] column is equal to the value in
p_crit1. If the attempted filter returns an empty set (i.e. there is no match), then just return the entirety of
full_data. This means if a user enters an invalid value in
G2) or enters nothing in that cell, we will just get all the data.
Now, filter the data returned by the first filter (called
qcrit1) where the third column (accessed via
INDEX) is equal to the value in
p_crit2. If the attempted filter returns an empty set (i.e. there is no match), then just return the entirety of
qcrit1. This means if a user enters an invalid value in
H2) or enters nothing in that cell, we will just get whatever was returned by
qcrit (the first filter).
The net result is that we can change the values in cells
H2 and see the filtered dataset change.
The subtotal formulas are above the filtered dataset so that it has room to change shape depending on the amount of data in
full_data and what query criteria are selected. They are applying the sub-total to the 4th and 5th columns of the dynamic array reference
$G$6#, which will automatically pick up all the data returned by the filter. You can of course use
MATCH in place of hard-coding the column indexes 4 and 5 if you want to.
The benefit of this approach is that you can now easily refer to the
p_crit2 in as many places in your workbook as you want. You can optionally add data validation to cells
H2 to limit the values the user can select. These can then be all possible Crit1 and Crit2 values, or a subset of each.