Syntax of field "Google Sheets / Pivot table editor / Filters / Filter by condition / Custom formula is"

Question

In Google Sheets, if I create a pivot table, there is a Pivot table editor I can use to configure the pivot table.

The lowest section of the interface of the editor is Filters (which allows me to add filters). When I add a filter, I have to choose a column from the source data. Having chosen a column, I am able to Filter by condition and Filter by values.

By values is easy enough - it presents a check list. By condition also works fine with options like Text contains and Text ends with. But the final option in the by values list is Custom formula is. This is the option that is of interest to me, but it is also not fully documented on the help page (https://support.google.com/docs/answer/7572895)

What is the syntax of the formula? How do I refer to the row, in the source range of the pivot table, to which the filter is being applied? I have done a test in which I enter = M3 = 5 and if I put the value 5 in cell M3 of the sheet in which the pivot table sits (not the sheet in which the source range resides), then the filter is all-pass, otherwise it is no-pass. What is the point of that? How do I refer to the value of the cell in question? That would be very useful! Then I could use a formula like = AND(<val>!="Financial", <val>!="Income") and other such constructs, but at the moment it (the Custom formula is option) is almost useless.

Answer 1

Well, it's clunky, but this works: INDIRECT("R" & ROW() & "C" & COLUMN(),false)

e.g. = AND(INDIRECT("R" & ROW() & "C" & COLUMN(),false) <> "Financial",INDIRECT("R" & ROW() & "C" & COLUMN(),false) <> "Income")

I could make it a lot neater if I could pack some of this into a custom function, but I can't seem to call any of INDIRECT, ROW(), or COLUMN() from a custom function, nor call a custom function successfully from within the formula. I'd appreciate any tips on that.

P.S. It seems that once you start wanting this level of features, you've probably reached the threshold beyond which the general answer is to look at the more powerful QUERY function, which can produce pivot tables and also supports named ranges and more.

Answer 2

I was able to do what i needed with =and(date(2019, 7, 1) <= 'Expense Date', 'Expense Date' < date(2020, 7, 1))

in your case, i would expect that is =AND(<val> <> "Financial", <val> <> "Income")

<val> will need single quotes if it has a space in it

Answer 3

=and(date(2019, 7, 1) <= 'Expense Date', 'Expense Date' < date(2020, 7, 1))
=AND(<val> <> "Financial", <val> <> "Income")

This worked for me. The only difference is that I had to use ; instead of ,.