pivot table - subtotal below detail

[Guest]

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]

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.

 

[Guest]

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]

Try hiding some of the subtotals. There are instructions here:

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

 

[Guest]

Hi Debra,
I've tried to follow the instructions on that page, but waht I need is the
opposite: I need the subtotals at the end (4), but not the subtitles on each
ZIP code, which will be always 1 of course:

Ctry Code ZIP Data Total
DE 21442 Count of Part Desc 3
Sum of ZIP_count 1
52062 Count of Part Desc 6
Sum of ZIP_count 1
63739 Count of Part Desc 6
Sum of ZIP_count 1
94469 Count of Part Desc 6
Sum of ZIP_count 1
DE Count of Part Desc 21
DE Sum of ZIP_count 4

Is it possible to remove all the "Sum of ZIP_count" but leave the subtotal
"DE Sum of ZIP_count"? (otherwise a list that was almost 5.000 rows is now
10.000!!)

thanks!
Massimo

 

[Debra Dalgleish]

Drag the Data button onto the cell that contains the word Total, to
arrange the data horizontally. It's not exactly what you want, but will
result in a shorter list.

 

[Guest]

yes, it's a good compromise indeed.

thanks again
Massimo