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

**sangreal2007** · 06-21-2013, 09:34 AM

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.

**XOR LX** · 06-21-2013, 09:48 AM

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

**sangreal2007** · 06-21-2013, 10:59 AM

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!

**XOR LX** · 06-24-2013, 03:59 AM

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