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

 

[DRJ]

Like this?

Book1
	A	B	C	D	E
1	PurchaseOrder	Duplicates
2	PS04-120001	1
3	PS04-120001	0
4	PS04-120002	1		Total	7
5	5	1
6	1	1
7	5	0
8	2	1
9	3	1
10	4	1
11	3	0
Sheet1

 

[Brian from Maui]

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

If you can download Morefunc's addin, you can use COUNTDIFF,

Edit for Sumproduct

Book1
	A	B	C	D
1	ps04-120001		2	2
2	ps04-120002
3	ps04-120001
Sheet1

 

[scrainey]

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

This is not working it returnes a valve of 1 for some reason

 

[Brian from Maui]

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

This is not working it returnes a valve of 1 for some reason

Which formula are you using? You might also check for leading or trailing spaces in you column.

 

[Yogi Anand]

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

Hi scrainey:

Using the Data layout in column A of DRJ (Jacob's) post you could use ...

=SUMPRODUCT(--(COUNTIF(A2:A11,A2:A11)>1))

 

[Brian from Maui]

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

If the range contains any blank cells, either by omission or formula generated blanks (""), both Countif formulas will fail. Although having blank cells was not implied by the OP.

 

[scrainey]

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

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

Thanks for your help everyone, I got the above formula to work from a previous post.

http://www.mrexcel.com/board2/viewtopic.php?t=75526&highlight=count+duplicate

 

[justtin]

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

Hi,

I am trying to do the above and was reading this post. Where can I get the addin?

Cheers
Justin

 

[Yogi Anand]

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

Hi Justin:

You can get the MoreFunc add-in from ...

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