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

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

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

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