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

This is a discussion on How do I count the duplicate values in a range only once. within the Excel Questions forums, part of the Question Forums category; How do I count the duplicate values in a range only once. I would like to count the number of ...

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

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

2. Like this?

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B3B4E4B5B6B7B8B9B10B11 =

A
B
C
D
E
1
Purchase*OrderDuplicates***
2
PS04-1200011***
3
PS04-1200010***
4
PS04-1200021*Total7
5
51***
6
11***
7
50***
8
21***
9
31***
10
41***
11
30***
 Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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

Edit for Sumproduct

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: 11.0 : OS = Windows XP
 File Edit View Insert Options Tools Data Window Help About
 C1D1 =

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

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

4. ## 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

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

Originally Posted by scrainey
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.

6. ## 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))

7. ## 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.

8. ## 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/viewto...ount+duplicate

9. ## 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

10. ## 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/

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•