Pivot Table Question

[JAF7]

I'm looking for a way to display a count of of the number of hidden records represented by a rolled up field in a Pivot. For instance I have a pivot table for customers that groups them by A, B, or C. When the pivot table is rolled up it shows the data just for the groups (A, B, and C). For example the total sales for A customers, B customers, etc. I can click on the + sign by the A customers to see what customers are in that group.

What I'm looking to do is for the pivot to show me a COUNT of the customers that are hidden under that group (without expanding the group by cliciing the +). Ideally putting this in a field that I could also do calcs on. For instance there are 20 customers in Group A, 70 customers in Group B, and 10 customers in Group C. I could then calculate the %'s they make up of the total, etc. I can get this outside of the pivot, but I'd like it within the pivot so it is dynamic as I change various filters.

Thank you for any help you can offer on this.

 

[texasalynn]

why not put the field in the data as a count, so you would have that field in the data twice. Once for the sum and another for the count

 

[JAF7]

The field I'm using is the customer code (1 unique for each customer, but each occurs multiple times in the source data). I have the customer code field in the row labels area of the pivot, so I couldn't get the idea above to work.

Any other suggestions.

 

[texasalynn]

I'm not getting a visual then, could you post a sample?

 

[JAF7]

Sorry for the confusion. Does the visual below help at all:

<TABLE style="WIDTH: 253pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=339 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2958" width=83><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3754" width=106><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2247" width=63><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3100" width=87><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; WIDTH: 62pt; COLOR: #376091; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" width=83 height=19>Posting Date</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; WIDTH: 79pt; COLOR: #376091; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" width=106>(All)</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 47pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=63></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=87></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #4f81bd 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #4f81bd 1pt solid; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=19></TD><TD style="BORDER-RIGHT: #4f81bd 0.5pt solid; BORDER-TOP: #4f81bd 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #4f81bd 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">Values</TD><TD style="BORDER-RIGHT: #4f81bd 1pt solid; BORDER-TOP: #4f81bd 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #4f81bd 1pt solid; COLOR: black; BORDER-BOTTOM: #4f81bd 1pt solid; FONT-FAMILY: Calibri; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=19>ABC-Sales</TD><TD style="BORDER-RIGHT: #4f81bd 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #4f81bd 1pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">Customer No </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #4f81bd 1pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">Sales$</TD><TD style="BORDER-RIGHT: #4f81bd 1pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #4f81bd 1pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">Sum of COGS</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 14.4pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=19>A</TD><TD style="BORDER-RIGHT: #4f81bd 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none"></TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: #376091; BORDER-BOTTOM: #dbe5f1 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>$429,928</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: #376091; BORDER-BOTTOM: #dbe5f1 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>$216,727</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 14.4pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=19>B</TD><TD style="BORDER-RIGHT: #4f81bd 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none"></TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: #376091; BORDER-BOTTOM: #dbe5f1 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>$83,265</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: #376091; BORDER-BOTTOM: #dbe5f1 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>$39,648</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; HEIGHT: 14.4pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=19>C</TD><TD style="BORDER-RIGHT: #4f81bd 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none"></TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: #376091; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>$22,331</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: #376091; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>$13,144</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #4f81bd 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #4f81bd 1pt solid; COLOR: black; BORDER-BOTTOM: #4f81bd 1pt solid; FONT-FAMILY: Calibri; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=19>Grand Total</TD><TD style="BORDER-RIGHT: #4f81bd 0.5pt solid; BORDER-TOP: #4f81bd 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #4f81bd 1pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #4f81bd 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #4f81bd 1pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>$535,524</TD><TD class=xl63 style="BORDER-RIGHT: #4f81bd 1pt solid; BORDER-TOP: #4f81bd 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #4f81bd 1pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>$269,519</TD></TR></TBODY></TABLE>

In my Excel file the A,B,andC have + signs by them, which when I click expand to show the list of customers (column B) in each group. In the view above, I'd like to see a count of these customers. For example, the A is in Cell A7. In Cell B7 I'm trying to get a count of the customers in the A group. In Cell B8 a count of the B group. And in Cell B9 a count of the customers in the C group.

(I'm using Excel 2007)

 

[James01]

Perhaps the solution is to duplicate the fields in the pivot table.

For example currently "Customer No" appears under "row labels". Also add it under "Values" and change value field setting to count.

 

[Greg Truby]

Just drop in a helper field/column along the lines of

• =N(COUNTIF(A$1:A1,A1)=1)

changing the A1 reference to whatever your column is for customer/account number. Then just drop the helper field on your pivot and sum @ 375° basting every ten minutes for 45 minutes or until golden brown; add salt to taste.

 

[JAF7]

I did try the above, and it works well when no filters are applied. However when I apply a page filter than my counts will be off. For example, if I filter on a date range that is after the first occurance of that customer, then the helper field has a zero count for that customer and thus a zero count in the pivot when filtered (even though that customer is represented in the pivot).

Any thoughts on how to still get a count for those customers?

 

[Greg Truby]

I did try the above, and it works well when no filters are applied. However when I apply a page filter than my counts will be off.

Ummm, yeah, that was a rather key little detail. I should have spotted your use of a filter field in your sample pivot.

I've spent about ½ an hour playing with various ideas for a help column and none are panning out. I'm not coming up with anything that will allow you to then do what you describe and use for a percent of column type result.

Sorry, but I just don't have any more time to donate...

 

[Greg Truby]

Sorry, I should have added some notes for anyone else that wants to take a crack at helping on this. Given the complexity of possible filters one really would need to use a UDF in the helper column that reads the structure of the page/filter field and then replicates the countif() logic -- in this case it's really more of a COUNTIFS().

And since the values within the dataset are essentially a circular reference into the pivot table, one would need to refresh the pivot table each time the filters change. I believe one could probably trap the worksheet's pivottableupdate event to automate the refresh.