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:


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.

enter image description here

  • you will need to either filter it out or query it out and then construct pivot table from there. this cant be done directly in pivot table. can you share a copy of your sheet?
    – user0
    Mar 5, 2019 at 7:50
  • Thanks for the quick reply Mark - just cleaned the document up and made it public - docs.google.com/spreadsheets/d/…
    – Adam Reed
    Mar 5, 2019 at 8:01
  • Sorry - just made it editable by anyone
    – Adam Reed
    Mar 5, 2019 at 8:04

2 Answers 2

  • 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))}



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.

Regex search

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.Caliper

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.