Pivots: Grand total = Count not sum of data

[Guest]

Hello,

Thats the question, I have a pivot that I want to count the total number of
unblank fields, there are about 10, but it takes the sum and shows like 300
in grand total. I need the grand total to say 10. Is this possible? I need
this because of data replication.

 

[Guest]

Have you tried right clicking on the Field and selecting Field Settings and
changing the aggregation to Count?

 

[Guest]

Yes I change the field setting to count but for some reason its still not
counting it right. It shows like 35, then when I highlight the field with my
cursor and show the count its like 11. I have no idea what to do.

 

[Guest]

Double click on the 35 to get a listing of all of the records it is
aggregating (assumes that you have Enable Drill to Detail checked in the
Table Options).

 

[Guest]

It shoots out 35 records but, there are duplicate records that I only want
counted once

 

[Debra Dalgleish]

There's no function in a pivot table that will count unique items. As a
workaround, you could add a field to the source data, to calculate the
count, and add that field to the pivot table. There's an example here:

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