Filtering Pivot table by multiple values

Question

I have a pivot table below that I am trying to filter dynamically based on a separate list and am trying to figure out how to do this by referencing the relevant field. In the below example I have the same data set and I would like to filter column B (referral) by G3:G4 (12.com and ab.com). Ideally, the final result would be the pivot table would display everything aside from "xyz.com" found under rt.com

In the pivot table editor I've tried to choose filter -> referral -> filter by condition by a few different ways including:

=regexmatch(B3,"12.com|ab.com")
=MATCH("ab",B3,1)=1

If I run the above formulas outside the pivot table's filter it works fine, but inside nothing seems to work, even replacing B3 with B:B

If anyone has any advice on how to get this to work I would be highly appreciative.

Answer 1

• use this formula to filter out stuff you need and then construct pivot table from there:

  ={E829:H829; FILTER(E830:H837, COUNTIF(B830:B837, G830:G837))}

Answer 2

Best way to do this is to filter first, then run a pivot table on the results.

Filtering efficiently.

Rather than editing a filter function directly, set it up to use a cell's contents with the criteria to use.

Case study:

I ran into this with a inventory supply list from a nursery company that came every two weeks. The list typically had 1000-2000 line items, and a given item could be at multiple locations in the list depending on what size pot it was in. This made generating quotes difficult.

The two pink cells accept a regex expression in simple form, this then is stuffed into the sort() call. "Formal" and "Common" are named ranges in the primary data sheet.

The regexes allow a fairly complex search. Ones I do often, I have just duplicated this sheet, and left the formula in place.

E.g. A caliper tree is any tree that is over 1" in diameter measured 6" above the ground. But some times they don't include the measurement. So the search contains several forms of matches for different ways that a caliper tree can be listed.