Count Unique Values

[Gustavo]

Hi Everyone,
I am using a pivot table to count the number of
ocurrences in a group. The problem is that the pivot
table counts the total number of rows (records I suppose)
that filters including the duplicate values. I need to
count the unique values only otherwise my total count is
overstated.
Can anybody help me or direct me how to go around this
problem that is driving me crazy? I use Excel 2000
Thanks in advance
Gustavo

 

[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 source table, then add that field to the pivottable.

For example, if you want to count unique items sold by each sales rep,
where rep name is in column C, and Item name is in column D:
=IF(SUMPRODUCT(($C$2:$C2=C2)*($D$2:$D2=D2))>1,0,1)

Copy this formula down to all rows in the database.

In the pivot table, add rep name to the row area, and add this field to
the data area, and you'll get a count of unique items.