Pivot Table - Count wNo Duplicates

[Jim]

I am using a spreadsheet that tracks new accounts opened
by for a month by sales rep. A rep may open one account
for a client or multiple accounts. For my pivot table I
want to show the new clients for a rep with a count of
total new accounts per client and the total number of new
clients by rep. Whe I use the count function I get a
count of total new accounts only because in the master
spreadsheet the client name appears for each account
opened. How do I account for the case of duplicates in my
total count of clients per rep? For the case of a client
that opens three accounts, I want that client to only
count once in the reps total new client count.

 

[Debra Dalgleish]

You could add a column (ClientCount) to the source data, and use a
formula to count the client occurrences.

For example, if the client name is in column B, enter the following
formula in cell C2:
=IF(COUNTIF(B$2:B2,B2)=1,1,0)

then copy the formula down to the last row of data.

Refresh the PivotTable, and add the new field to the data area.