Help. How do I do multiple Value Filters on a pivot table row label?

insanity82007

Board Regular
Joined
Oct 10, 2007
Messages
130
As the title suggests, how do I do multiple value filters on a row label in a pivot table in Excel 2007/2010?

I can do a label filter and a value filter in conjunction but this is not useful for what I'm trying to do. Does anyone know how I can filter on multiple value fields e.g. field 1 may be incidents from 1 to 10,000 and I want to see anything over 1,000 and field 2 may be a number of days from 1 to 1800 days and I only want to see anything over 365 days. I can't drop these 2 fields into a column, row or filter as they are calculated.

There isn't even and and/or option.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
You can apply a label filter separately for each rowfield by right-clicking on one of its label items.

This will be an Intesecting (Criteria1 "And" Criteria2) filtering.
I don't think PivotTables allow a Union (Field1 meets Criteria1 "Or" Field2 meets Criteria2) filtering of two fields.
 

insanity82007

Board Regular
Joined
Oct 10, 2007
Messages
130
You can apply a label filter separately for each rowfield by right-clicking on one of its label items.

This will be an Intesecting (Criteria1 "And" Criteria2) filtering.
I don't think PivotTables allow a Union (Field1 meets Criteria1 "Or" Field2 meets Criteria2) filtering of two fields.

I need to apply a value filter though, not a label filter.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Sorry, I misread your original post.

Yes, you can also add multiple Value filters.
Right-Click on your Row Label > Value Filters...
The dialog box should be titled Value Filter (your field name)
If you have multiple value fields, you should be able to dropdown the listbox under "Show items for which".

If you have more than one Row Field make sure that the Value Filter (your field name) title is at the correct level, as you can filter at different levels.

Like described above, those multiple filters will be handled as Intersecting filters (Criteria1 "And" Criteria2)
 

insanity82007

Board Regular
Joined
Oct 10, 2007
Messages
130

ADVERTISEMENT

Sorry, I misread your original post.

Yes, you can also add multiple Value filters.
Right-Click on your Row Label > Value Filters...
The dialog box should be titled Value Filter (your field name)
If you have multiple value fields, you should be able to dropdown the listbox under "Show items for which".

If you have more than one Row Field make sure that the Value Filter (your field name) title is at the correct level, as you can filter at different levels.

Like described above, those multiple filters will be handled as Intersecting filters (Criteria1 "And" Criteria2)

The issue I've got is that I don't have multiple value fields and I can't see how to add any:

valuefilter.jpg
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Are you saying that when you click the dropdown that now shows the field "Sum of dat1", there are no other fields listed in that dropdown list? There should be one for each Value field, including calculated fields.

You can only show one field at a time in this dialog, but by setting a criteria for each, the result is filtering for Criteria1 And Criteria2.
 

insanity82007

Board Regular
Joined
Oct 10, 2007
Messages
130
Are you saying that when you click the dropdown that now shows the field "Sum of dat1", there are no other fields listed in that dropdown list? There should be one for each Value field, including calculated fields.

You can only show one field at a time in this dialog, but by setting a criteria for each, the result is filtering for Criteria1 And Criteria2.

As soon as I set a criteria for each it replaces one with the other. They don't both work together with an intersect
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Well, I tested this yesterday before posting, and now I find myself unable to repeat those results. :oops:

When I applied the second criteria, it definately did an Intersecting filter. The the only explanation I can think of is that I might have inadvertantly applied criteria1 for Sum of dat1 to one rowfield; and criteria2 for Sum of dat2 to a different rowfield.

If that's the case, I failed to follow my own caution...
If you have more than one Row Field make sure that the Value Filter (your field name) title is at the correct level, as you can filter at different levels.

I've tried a number of different ways to do this including VBA, and haven't found a solution.
Web search hasn't yielded any clues.

I'll keep looking...I apologize if I gave you incorrect information.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top