How to filter by subtotal exceeding $5000 - Pivot Table or by VBA (updated dataset)

Occasional Contributor

Hello everyone, 

 

I was wondering in a Pivot Table, how can I filter/use a slicer to display all individuals who have a subtotal over $5000 per year. 

I did conditional formatting to identify such individuals (highlights subtotals in red) but the pivot table would not let me isolate them to show only the individuals with subtotals exceeding $5000 per year. 

Please see the picture below for a visual example. I am not sure how to attached the excel file with the pivot table but I pasted the table below. Thanks.

 

I also tried to right-click on the Pivot table containing the First Name and clicked on Filter, then Value Filters and sent the filter to Sum of Amount is greater than and type 5000 but it also shows if the grand total of the individual is greater than $5000 for all years instead of isolating for the subtotal of each year and checking that. Please see below. Thanks.

 

If you can provide VBA code or share how it would be possible via the Pivot table, that would be awesome. Thank you. 

Kind regards,

Zander

 

zander140_0-1655647363203.png

Value filter results(shows if grand total for an individual is greater than $5000 instead of per year):

zander140_1-1655647437077.png

 

Dataset(since I am not able to post the Excel File):

First Name Last NameTrip PriceYear

Esther AllanUS1806.452019-04-19
Esther AllanCanda359.262019-07-20
Esther AllanItaly5078.452022-05-04
John SmithUS2000.262021-02-02
John SmithPoland2500.452022-05-05
John SmithJapan3516.562021-05-07
John SmithJapan3500.892022-07-04
Ayana GrantCanada653.232021-03-05
Ayana GrantCanada756.232022-04-09
Ayana GrantUS4000.562021-02-25
JimmyLeeCanada455.232019-04-19
JimmyLeeCanada454.562021-05-02
JimmyLeeCanada670.212021-07-02
2 Replies
Yes, I was trying to remove the last one as this version is most insightful regarding the question. This is why I labelled it as (updated dataset). Thanks.