How to filter on one column, then "lock" a filter on another, then "unlock" the filter on the original

[xcellerator]

I am sure this has been asked and answered here before already, but I'm not even sure what terms to use...as you can tell from my subject header!

I have a table like this - only it is massive...tens of thousands of rows, and dozens of columns.

Customer	Product	Revenue
Bob	Apple	10
Bob	Orange	4
Annie	Banana	8
Sam	Apple	7
Sam	Fig	3
Katie	Banana	8
Katie	Grapes	5

I am looking to filter/pivot/dosomethingelse to get the data to show only the customers that purchased Apples, but also show everything they purchased (so not filter to just Apples).
So it would look like this:

Customer	Product	Revenue
Bob	Apple	10
Bob	Orange	4
Sam	Apple	7
Sam	Fig	3

Then after this my I would want to pivot this table, so I could see the total revenue for these "filtered" Customers (so just Bob and Sam), or the total revenue by Product, etc.

So basically what I would think I would do is:
1. filter Product for Apple
2. then somehow filter Customer for Bob and Sam and keep it locked to just those 2
3. remove Product filter
But I can't figure out how to do what I would think is Step 2.

Maybe I should even be using Pivots for the whole thing...not sure.
Thank you for any advice.

 

[xcellerator]

I'm getting closer (and now trying to use XL2BB! )
The only part where I am getting stuck is on the output...it shows 2 rows of Katie...but I'm not sure why.

It should be outputting all the purchase rows of any customer that purchased either Apples or Grapes, so...
2 Bob rows (since Bob bought an Apple)
2 Sam rows (since Sam bought an Apple)
2 Katie rows (Since Katie purchased Grapes)

mrexcel-filter.xlsx
	A	B	C	D	E	F
1	Customer	Product	Revenue
2	Bob	Apple	10		TRUE	Apple
3	Bob	Orange	4		FALSE	Grapes
4	Annie	Banana	8
5	Sam	Apple	7
6	Sam	Fig	3
7	Katie	Banana	8
8	Katie	Grapes	5
9
10
11
12
13	Customer	Product	Revenue
14	Katie	Banana	8
15	Katie	Grapes	5
Sheet2

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,$F$2)>0
E3	E3	=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,$F$3)>0

 

[Fluff]

Is the criteria range E1:E3?

 

[xcellerator]

Oi.... E2:E3

Corrected and now works. I'll continue to add other criteria in column E, probably up to E20 or so, and then set Criteria as E1:E20 (I had not suspected I needed to include that blank cell).

*MUCH* appreciation Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[xcellerator]

Just as reference (for my notes as much as anything)...takes 3 minutes to run for my set (15k rows x 40 columns)
Which is totally fine since I really only need to run it once for each exercise.