Back to Pivot Tables in Excel archive index

Back to archive home

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:

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

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?

...there's nothing comparable to COUNT(DISTINCT x)

for PivotTables.

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.

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.