Count only once

[rexmann]

Hi

Is there an option to count a record only once. What i mean is if the word
is repeated in the list it only counts it once. Exampl

London
London
Brimingham
Manchester
London

This would give a count of 3 (only including the London once)

Not sure if this is possible

Any help greatly appreciated

rexmann

 

[Dave Peterson]

One way:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

(Adjust the range to match.)

 

[Gary''s Student]

See:
Counting Distinct Entries In A Range
in:
http://www.cpearson.com/excel/Duplicates.aspx

 

[rexmann]

Cheers Both

nice one, thank you very much

Rexmann