Count distinct

[Debbie t]

Is there a simple way I can count only one occurence of a text value in a
list where there are several duplicates.
Thanks

 

[mzehr]

Hi Debbie,
Try the following entered as an array (Shift-Ctrl-Enter):
=SUM(1/COUNTIF(Range1,Range1))
For example if you have
a
b
a
c
d
e
s
w
a

in column a
then =SUM(1/COUNTIF(A1:A9,A1:A9)) entered as an array will properly return 7

 

[mzehr]

Debbie,
I should probably have commented about using a pivot table.
That will summarize very nicely all your data, and give you a count of
howmany occurances each item has as well.
For some excellent resources on pivot tables see:

http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.contextures.com/xlPivot01.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm