Archive of Mr Excel Message Board


Back to Pivot Tables in Excel archive index
Back to archive home

Pivot Table - Count distinct values within a group

Posted by Daniel on September 25, 2001 2:00 AM
Hi,
Is there a way to count the distinct occurrancies of a value within a group? For example how many different customers. Here's some some sample data:

Customer Period Amount
A 1 10
A 1 12
A 3 11
B 1 15
B 1 11
B 2 13
B 3 14
C 3 10

Pivot Table:

Customer (All)

Sum of Amount
Period Total # of Customers
1 48 2 <=<br>2 13 1 <=<br>3 35 3 <=<br>Grand Total 96 3 <=<p>Is there a way to create a formula/calculated field to get the # of customers as shown above?

Any ideas are greatly appreciated.
Daniel


See 30837.html (nt)

Posted by Mark W. on September 25, 2001 7:04 AM


Re: Thanks but it doesn't work

Posted by Daniel on September 26, 2001 12:30 AM
Thanks Mark. Interesting idea, but it doesn't work in my case. It correctly counts the number of distinct customers in the grand total. But if I make the period a row field, then I get wierd results. I think this cannot be done on the data level, since the result always depends on the grouping. The way to do this in SQL terms, would be to COUNT(DISTINCT FieldName) for whatever records are part of the current grouping. For example, if I double-click on a data field, then Excel displays the detail records in a separate sheet. A COUNT(DISTINCT FieldName)) against these detail records would always return the correct result, no matter how the grouping is done. Unfortunately, I don't see how to do this. The most logical place would be in the options for a data field.

Any other ideas?


Unfortunately...

Posted by Mark W. on September 26, 2001 4:13 PM
...there's nothing comparable to COUNT(DISTINCT x)
for PivotTables.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.