# When filtering data, is it possible to put in a cell the value of the filter? It works very well and lets me see the total when I filter either with criterion 1 or 2 (or both). Now, I would like to have in cells `B8` and `C8` the "selected" value in the filter: and if I change the filter: ...and similarly on the `B8`. I do not mind if, when selecting just Crit1, the `B8` cell is blank or with a random value of the list. In the real application, the `C8` cell value will be used to index inside another table.

The idea is "put in `B8` what is displayed two rows above, minding the filtering" --- is that possible in `calc`? (Or `excel`, I do not mind switching).

• You can upload the file elsewhere and edit the question to include the link. Mar 18, 2022 at 11:01
• @Gantendo thanks, done (I was not sure if it's accepted practice here) Mar 18, 2022 at 11:11
• Sorry, my answer was not to the point. If you wish to get the last non-blank value in column C rows 2-6, you may use in C8 the formula `=LOOKUP(2,1/(1-ISBLANK(C2:C6)),C2:C6)`. Does that work for you? Mar 18, 2022 at 12:30
• @harrymc I tried it, but it says always `b`, no matter the filtering... Mar 18, 2022 at 13:40

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 `G2` and `H2` and called them `p_crit1` and `p_crit2`. The `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.

In cell `G5` I have this formula to get the headers:

``````=full_data[#Headers]
``````

In cell `G6` I have this formula to get the filtered data:

``````=LET(qcrit1,FILTER(full_data,full_data[Crit1]=p_crit1,full_data),FILTER(qcrit1,INDEX(qcrit1,,3)=p_crit2,qcrit1))
``````

Let's look at this more closely:

``````=LET(
qcrit1,
FILTER(
full_data,
full_data[Crit1]=p_crit1,
full_data
),
FILTER(
qcrit1,
INDEX(qcrit1,,3)=p_crit2,
qcrit1
)
)
``````

Let the name `qcrit1` (query by criteria 1) be the result of the `FILTER` of `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 `p_crit1` (cell `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 `p_crit2` (cell `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 `G2` and `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_crit1` and `p_crit2` in as many places in your workbook as you want. You can optionally add data validation to cells `G2` and `H2` to limit the values the user can select. These can then be all possible Crit1 and Crit2 values, or a subset of each. • Really nice! A pity that OO Calc has no FILTER... ;-) Mar 18, 2022 at 15:36

You can achieve it with a helper column and `TEXTJOIN()` available in Excel 365:

• formula in helper column: `=SUBTOTAL(2,D4)`
• formula for totals row: `=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(B2:B7,\$E\$2:\$E\$7=1)))`
• formula for sum: `=SUM(FILTER(D2:D7,\$E\$2:\$E\$7=1))`

of course you can even hide your helper column  • Nice, thanks. A pity UNIQUE and FILTER are not in LO calc... Mar 18, 2022 at 15:34