Home |
Search |
Today's Posts |
#1
|
|||
|
|||
pivot table - subtotal below detail
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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" wrote: 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 |
#6
|
|||
|
|||
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. Massimo wrote: 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" wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
|
|||
|
|||
yes, it's a good compromise indeed.
thanks again Massimo "Debra Dalgleish" wrote: 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. Massimo wrote: 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" wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a formula to a pivot table | Excel Discussion (Misc queries) | |||
Pivot Table services | Excel Discussion (Misc queries) | |||
How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) | |||
pivot table multi line chart | Charts and Charting in Excel | |||
convert excel list to pivot table | Excel Discussion (Misc queries) |