Pivot table "restricted" output

[Guest]

Hi,
In the row area I have the fields Product, Country and Month.
In the data area I have the fields Sales_Act (Sum of) and Sales_Bdg (Sum of).
How can I get in pivot table the only products, countries and monthes that
have Sum of Sales_act between $ 9,000 and $ 40,000?

Thanks

 

[Debra Dalgleish]

You could filter the values in Excel 2007, but if you're using an
earlier version, that feature isn't available.

For earlier versions, you could add a field to the source data, to
calculate the total for each product/country/month combination. For example:


=SUMPRODUCT(--($G$2:$G$50=G2),--($D$2:$D$50=D2),--($F$2:$F$50=F2),$L$2:$L$50)

where product is in column G, country in D, month in F and sales in L.

In another column, test the results against your minimum and maximum
values, e.g.:
=AND(H2>=MinVal,H2<=MaxVal)

Add the test field to the page area, and select TRUE from the dropdown list.