pivot table, functions for subtotals

[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 function in a
calculated field?

any help very much appreciated.

Massimo

 

[Guest]

Hi,

The count of zip codes cant be computed as long as it is put as a
column.Count can be computed only when the zip codes are there in the data
area.

Hence re-arrange the layout of your pivot like this - Country & Zip codes in
column area and choose zip code again in the data area(select 'Count' in the
summarise by Pivot field) and choose orders again in the data area.This way
you will have a table like this:

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

Hope this helps.

Govind.

 

[Guest]

Hi Govind,

Thanks a lot for your help. I've tried to follow your instructions, but the
result is that the count for the zip codes gives the same values as the count
for the orders. Apparently excel does not count the ZIP codes considerg their
uniqueness, it counts them even when the ZIP does not change, so in the end
there are as many ZIP counted as orders...
any idea?

if you want I can send you the file...

thanks a lot.

Massimo