Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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. #1
    Board Regular
    Join Date
    Apr 2004
    Location
    Zachary, LA
    Posts
    67

    Default 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. #2
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    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
    =

    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. #3
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,277

    Default 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

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

    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. #4
    Board Regular
    Join Date
    Apr 2004
    Location
    Zachary, LA
    Posts
    67

    Default 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. #5
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,277

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

    Quote 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. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default 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))
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,277

    Default 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. #8
    Board Regular
    Join Date
    Apr 2004
    Location
    Zachary, LA
    Posts
    67

    Default 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. #9
    New Member
    Join Date
    Apr 2004
    Posts
    18

    Default 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. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default 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/
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com