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

[insanity82007]

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.

 

[Jerry Sullivan]

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]

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]

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]

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:

 

[Jerry Sullivan]

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]

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]

Well, I tested this yesterday before posting, and now I find myself unable to repeat those results.

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.