Pivot Table Count

[JohnV]

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]

You can add a field to the source data, as described here:

http://www.contextures.com/xlPivot07.html#Unique

and use that field to count the divisions per client.

 

[JohnV]

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]

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.