+ Reply to Thread
Results 1 to 4 of 4

Counting the number of fields in a column for which there are duplicate values.

  1. #1
    Registered User
    Join Date
    12-13-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Counting the number of fields in a column for which there are duplicate values.

    Help would be appreciated. It seems simple to me but I can't get it.

    1 blue formula = 2
    2 green
    2 red
    3 yellow
    3 blue
    4 cyan
    5 magenta

    The formula should equal two because there are two values in column A that have been repeated (2 and 3). What I need is a count of only non-unique values in Column A.

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Counting the number of fields in a column for which there are duplicate values.

    Hi,

    This can't be the most efficient formula in the world and I expect somebody to come up with something far simpler (and less resource hungry), but assuming your data is in cells A1:A1000:

    =SUMPRODUCT(--(1/COUNTIF($A$1:$A$1000,$A$1:$A$1000)<1)/COUNTIF($A$1000:$A$1000,$A$1:$A$1000))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    12-13-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Counting the number of fields in a column for which there are duplicate values.

    Thanks, that's a quick fix for sure. It won't help much in the long run because it's being used in a way where I'm continually adding rows to the data. So I'd have to go back and edit that formula every time I added a row (which is often).

    I'd love a version that could include the range $A:$A. That way I would have to worry about changing the size of the range.

    Thank you again!

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Counting the number of fields in a column for which there are duplicate values.

    Ok, so best way is to use the Name Manager to create a Named Range using the OFFSET function, which will respond dynamically as you wish.

    I'll amend my previous assumption slightly and assume that you have a header in cell A1 so that your range of numbers actually begins in A2 (obviously you need to amend this according to your situation).

    In Name Manager (Formulas tab), define a new Name (called e.g. "Range1") and enter this formula in the Refers to: box:

    =OFFSET(Sheet1!$A$2,,,COUNT(Sheet1!$A:$A),)

    NOTE: this obviously assumes your sheet is called Sheet1. It also assumes that all the entries in the column are numerical (apart from the header, which must be non-numerical).

    Once done, your formula now becomes:

    =SUMPRODUCT(--(1/COUNTIF(Range1,Range1)<1)/COUNTIF(Range1,Range1))

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1