To count DISTINCT records in a PIVOT TABLE

[brouts]

Hello everybody,

I have read the last posts on counting issues in pivot tables but I have found no clue for my trouble.

I'm using a "Count of xxxx" in a pivot table and it is counting the number of records of this column or category.
What should I use if I want to count the number of distinct records??

I cannot find such an option.
I am using Excel 2000.

Thank you in advance for your help,
Ludovic

 

[PaddyD]

example of the source data, pivot table & desired solution please.

 

[Andrew Poulsom]

To count the distinct records you need to add a column to your source data, eg:

BOOK2
	A	B	C	D	E	F	G
1	Name	Value	Distinct			Data
2	Andrew	1	0.5		Name	SumofValue	SumofDistinct
3	Bill	2	1		Andrew	6	1
4	Fred	3	0.5		Bill	2	1
5	John	4	1		Fred	9	1
6	Andrew	5	0.5		John	4	1
7	Fred	6	0.5		GrandTotal	21	4
Sheet1

The formula in C2 is:

=1/COUNTIF(A:A,A2)

copied down.

 

[brouts]

Fantastic tip Master Andrew !!
Thank you for this silver bullet...

Ludovic

 

[dgacorp]

Solution with helper column added (like suggested by Andrew Poulsom) works if the pivot hasn't page fields. In fact if there is a Page Field, and with it you filter the data in pivot, sum of "distinct" is no more correct. Suppose to add a column "D" with values S or N, and header "test", add "test" to page fields of the pivot, and use the field to choose only "S" or only "N". The pivot counts "distinct" only when a row respects the page field value "S" or "N", so the sum of "distinct" is no more "1" for Andrew etc.
Any workaround to this big problem???