How do I count the duplicate values in a range only once.

[scrainey]

How do I count the duplicate values in a range only once.

I would like to count the number of purchase orders in column "A", and some cells will contain duplicates, I only want to count the duplicates once, the remaining duplicates should not be included in the count.

example:

PS04-120001
PS04-120001
PS04-120002
___________

= 2

Any help would be appreciated

 

[scrainey]

Re: How do I count the duplicate values in a range only once

I tried to do a Google search for Brian's post on Morefunc's Add-in and got nothing, but this formula will do what you want just change the range.

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

Steve

 

[Yogi Anand]

Re: How do I count the duplicate values in a range only once

Hi Steve:

I just responded to Justin's inquiry about the MoreFunc Add-in -- you can get it from ...

http://longre.fr.free/english/

 

[scrainey]

Re: How do I count the duplicate values in a range only once

Yogi

Thanks for the reply but the link is not working.

Steve

 

[Yogi Anand]

Re: How do I count the duplicate values in a range only once

My apologies for the typo -- it is ...

http://longre.free.fr/english

and I checked -- it is working!

 

[dwbolland]

Re: How do I count the duplicate values in a range only once

does anyone have the scripting for the countdiff addin - as i can't download it on this darned computer!

 

[Aladin Akyurek]

Re: How do I count the duplicate values in a range only once

does anyone have the scripting for the countdiff addin - as i can't download it on this darned computer!

COUNTDIFF is a compiled function from the morefunc add-in. If you can't have the add-in for some reason, you can always use a formula for distinct/unique count with built-in functions.