Try hiding some of the subtotals. There are instructions he

http://www.contextures.com/xlPivot05.html

Massimo wrote:
Hi Debra,
thanks a lot!
although I can't understand completely how the sumprod function works, in
the pivot table I have now the ZIP codes counted. One last thing: the list is
extremely long, and now for every zip there are 2 rows, one to count the
orders (like before), and another one to count the zip, which displays always
1 obviously. Like this the list has become twice longer than before; is there
a way to hide the zip count row in the orders sections, but leave the 'sum of
zp count' at the end of every country?

thanks again
Massimo


"Debra Dalgleish" wrote:


You could add a column to the list, then add that field to the pivottable.

For example, to count zips per country, where Country is in column A,
and zip is in column B:
=IF(SUMPRODUCT(--($A2:A3=A3),--($B2:B3=B3))=1,1,0)

Copy this formula down to all rows in the list.

In the pivot table, with Country and zip in the row area, add this new
field to the data area, and you'll get a count of unique zips per country.


Massimo wrote:

Hi all,

I need help with a pivot table. This is what I have:
(country and zip code are Column Area, while orders is Data)

COUNTRY ZIP CODE ORDERS
DE 1345 10
DE 1485 5
DE 1887 12
DE total 27
IT 20145 4
IT 20455 9
IT total 13
Grand total 40

And this is what I need:
(below the ZIP CODE column, in the row where there is the subtotal for the
orders, I need the count of how many zip codes are considered per country)

COUNTRY ZIP CODE ORDERS
DE 1345 10
DE 1485 5
DE 1887 12
DE total 3 27
IT 20145 4
IT 20455 9
IT total 2 13
Grand total 40

Does anybody know how to obtain this result? Maybe with a calculated field?

any help very much appreciated.

Massimo


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html