|
Pivot Table Count
Hello. I have a pivot table that looks like
Client Name Div 1 Sales Div 2 Sales Div 3 Sales Div 4 Sales One Corp 100 200 250 65 XYZ Corp 155 100 Smith & Jones 50 20 75 What I want is to add the Count of Divsions like this Client Name Divs Div 1 Sales Div 2 Sales Div 3 Sales Div 4 Sales One Corp 4 100 200 250 65 XYZ Corp 2 155 100 Smith & Jones 3 50 20 75 Is there a way I can do this in a single pivot table or by combining two pivot tables? Thanks, JohnV |
Pivot Table Count
You can add a field to the source data, as described he
http://www.contextures.com/xlPivot07.html#Unique and use that field to count the divisions per client. JohnV wrote: Hello. I have a pivot table that looks like Client Name Div 1 Sales Div 2 Sales Div 3 Sales Div 4 Sales One Corp 100 200 250 65 XYZ Corp 155 100 Smith & Jones 50 20 75 What I want is to add the Count of Divsions like this Client Name Divs Div 1 Sales Div 2 Sales Div 3 Sales Div 4 Sales One Corp 4 100 200 250 65 XYZ Corp 2 155 100 Smith & Jones 3 50 20 75 Is there a way I can do this in a single pivot table or by combining two pivot tables? Thanks, JohnV -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Pivot Table Count
Hello Debra,
Thanks for the reply. I looked over the post, but I don't think it meets my specific needs. If I could post a mockup xls it would be clearer, but I've copied the format into the message. For each Division, there are multiple Data elements that I am placing side-by-side instead of in the row-by-row format. I would like the Count of Unique Divisions to be listed as a Row Element the same as the Client Name is a Row Element. The goal is that if they choose not to show some of the divisions, the count would only include those divisions that are visible in the pivot table / view. This is a sample view of the current pivot table: Division Data Div 1 Div 2 Div 3 Div 4 Div 5 Div 6 Rank Overall Client Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div 1 XYZ Company 10,383,486 1 453,537 26 186,802 57 11,387,680 1 2 Widgets Inc 1,857,607 4 1,364,993 1 141,481 86 856,363 27 18,160 61 3 ABC Corp 1,071,932 19 287,981 47 283,651 24 900,471 21 4 The Big Co. 1,758,873 5 1,003,512 4 1,526,769 9 129,343 4 5 Any Business 657,454 51 351,141 33 110,780 135 1,966,627 2 195,494 2 6 Small Group 2,439,934 2 164,107 100 543,166 9 1,360,256 10 7,542 93 This is a sample of what I would like to see: Division Data Div 1 Div 2 Div 3 Div 4 Div 5 Div 6 Rank Overall Client Divisions Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div 1 XYZ Company 5 10,383,486 1 453,537 26 500,250 1 186,802 57 11,387,680 1 2 Widgets Inc 5 1,857,607 4 1,364,993 1 141,481 86 856,363 27 18,160 61 3 ABC Corp 3 1,071,932 19 287,981 47 900,471 21 4 The Big Co. 5 1,758,873 5 1,003,512 4 450,003 2 1,526,769 9 129,343 4 5 Any Business 4 657,454 51 110,780 135 1,966,627 2 195,494 2 6 Small Group 6 2,439,934 2 164,107 100 275,025 3 543,166 9 1,360,256 10 7,542 93 Thanks, JohnV "Debra Dalgleish" wrote: You can add a field to the source data, as described he http://www.contextures.com/xlPivot07.html#Unique and use that field to count the divisions per client. JohnV wrote: Hello. I have a pivot table that looks like Client Name Div 1 Sales Div 2 Sales Div 3 Sales Div 4 Sales One Corp 100 200 250 65 XYZ Corp 155 100 Smith & Jones 50 20 75 What I want is to add the Count of Divsions like this Client Name Divs Div 1 Sales Div 2 Sales Div 3 Sales Div 4 Sales One Corp 4 100 200 250 65 XYZ Corp 2 155 100 Smith & Jones 3 50 20 75 Is there a way I can do this in a single pivot table or by combining two pivot tables? Thanks, JohnV -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Pivot Table Count
You could use the technique shown at that link to calculate the
divisions per client. However, when you add the field to the data area, it would create a column for each division, and a grand total count at the far right. You could manually hide the division count columns, and leave only the grand total column visible. JohnV wrote: Hello Debra, Thanks for the reply. I looked over the post, but I don't think it meets my specific needs. If I could post a mockup xls it would be clearer, but I've copied the format into the message. For each Division, there are multiple Data elements that I am placing side-by-side instead of in the row-by-row format. I would like the Count of Unique Divisions to be listed as a Row Element the same as the Client Name is a Row Element. The goal is that if they choose not to show some of the divisions, the count would only include those divisions that are visible in the pivot table / view. This is a sample view of the current pivot table: Division Data Div 1 Div 2 Div 3 Div 4 Div 5 Div 6 Rank Overall Client Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div 1 XYZ Company 10,383,486 1 453,537 26 186,802 57 11,387,680 1 2 Widgets Inc 1,857,607 4 1,364,993 1 141,481 86 856,363 27 18,160 61 3 ABC Corp 1,071,932 19 287,981 47 283,651 24 900,471 21 4 The Big Co. 1,758,873 5 1,003,512 4 1,526,769 9 129,343 4 5 Any Business 657,454 51 351,141 33 110,780 135 1,966,627 2 195,494 2 6 Small Group 2,439,934 2 164,107 100 543,166 9 1,360,256 10 7,542 93 This is a sample of what I would like to see: Division Data Div 1 Div 2 Div 3 Div 4 Div 5 Div 6 Rank Overall Client Divisions Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div Net Sales Rank Div 1 XYZ Company 5 10,383,486 1 453,537 26 500,250 1 186,802 57 11,387,680 1 2 Widgets Inc 5 1,857,607 4 1,364,993 1 141,481 86 856,363 27 18,160 61 3 ABC Corp 3 1,071,932 19 287,981 47 900,471 21 4 The Big Co. 5 1,758,873 5 1,003,512 4 450,003 2 1,526,769 9 129,343 4 5 Any Business 4 657,454 51 110,780 135 1,966,627 2 195,494 2 6 Small Group 6 2,439,934 2 164,107 100 275,025 3 543,166 9 1,360,256 10 7,542 93 Thanks, JohnV "Debra Dalgleish" wrote: You can add a field to the source data, as described he http://www.contextures.com/xlPivot07.html#Unique and use that field to count the divisions per client. JohnV wrote: Hello. I have a pivot table that looks like Client Name Div 1 Sales Div 2 Sales Div 3 Sales Div 4 Sales One Corp 100 200 250 65 XYZ Corp 155 100 Smith & Jones 50 20 75 What I want is to add the Count of Divsions like this Client Name Divs Div 1 Sales Div 2 Sales Div 3 Sales Div 4 Sales One Corp 4 100 200 250 65 XYZ Corp 2 155 100 Smith & Jones 3 50 20 75 Is there a way I can do this in a single pivot table or by combining two pivot tables? Thanks, JohnV -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| All times are GMT +1. The time now is 12:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com