Piviot Tables and Unique record count

[Gail McKee]

Here is a sample of the data from my piviot table.
DICK HUFNAGEL
COLORADO PUBLIC EMPLOYEES RETIREMENT 2
DELAWARE MANAGEMENT BUSINESS TRUST 2
JANUS CAPITAL MANAGEMENT LLC 1
JOHN HANCOCK FINANCIAL SERVICES, INC 1
KORNITZER CAPITAL MANAGEMENT 1
PENN MUTUAL LIFE INSURANCE COMPANY 1
TEACHERS INSURANCE & ANNUITY ASSOC. 2
THREE PEAKS CAPITAL MANAGEMENT LLC 1
UNITED FIDELITY LIFE INSURANCE CO. 2
DICK HUFNAGEL Total 13

The piviot table is counting each occurance for each
record. My goal is to get a total of unique records. So
in this instance is should be 8 not 13.

 

[Guest]

Harry,
That is what I did get get the results listed below. I
guess my probelm is that COLORADO PUBLIC EMPLOYEES
RETIREMENT does appear twice in my data but I only want it
to count once. Does this make sense? Can I do this
within the piviot table?

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/duplicat.htm#CountingUnique

 

[Debra Dalgleish]

A pivot table won't calculate a unique count. However, you could add a
column to the database, then add that field to the pivottable.

For example, if you want to count the customers in column A, use the
following formula in row 2:
=IF(COUNTIF(A$2:A2,A2)=1,1,0)

Copy this formula down to all rows in the database.

Add this field to the pivot table data area, as a Sum, and you'll get a
count of unique items.

 

[Gail McKee]

Frank
This maybe helpful, but can you tell me how to incorporate
these types of formulas into a piviot table. Do I need to
use a helper column?

Thanks