Filter Google Sheets' pivot table by comparison with column

Question

I want to filter a pivot table in the following set up:

My Table:

Key Value1  Value2
1   23      a
2   33      b
3   1       c
4   5       d

My pivot table (simplified):

Key SUM of Value1   COUNTA of Value2
1   23              1
2   33              1
3   1               1
4   5               1
Grand Total 62      4

I now want to filter the pivot table by the values in this list:

Keys
1
2
4

So the resulting pivot table should look like this:

Key SUM of Value1   COUNTA of Value2
1   23              1
2   33              1
4   5               1
Grand Total 61      3

I thought this should be possible by using a custom formula in the pivot filter but it seems I have no way of using the current cell in the pivot e.g. to make a lookup.

I created a simple example of this setup here: https://docs.google.com/spreadsheets/d/1GlQDYtW8v8ri5L68RhryTZxwTikV_NXZQlccSI6_7pU/edit?usp=sharing

player0 player0 · Accepted Answer · 2019-02-26T08:44:20

paste this formula in Filters!B1:

=ARRAYFORMULA(IFERROR(VLOOKUP(A1:A, Table!A1:C, {2,3}, 0), ))

and create a resulting pivot table from there:

demo spreadsheet

Filter Google Sheets' pivot table by comparison with column

1 Answers