Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table "restricted" output
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table "restricted" output
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. mit05 wrote: 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 Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find the "Show Pages..." Pivot table feature in 2007? | Excel Discussion (Misc queries) | |||
Deleted the pivot table - continue to get the "overlap" error message | Excel Discussion (Misc queries) | |||
bunch of "yes" or "no" entered in row, output 1 if a single yes | Excel Worksheet Functions | |||
"Show Field List" in Pivot Table Toolbar doesn't work | Excel Discussion (Misc queries) | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |