Quick, clean, and to the point

List contains duplicates

Excel formula: List contains duplicates
Generic formula 

If you want to test a list for duplicates, you can do so with a formula that uses COUNTIF together with SUMPRODUCT.

In the example, there is a list of names in the range B3:B11. If you want to test this list to see if there are duplicate names, you can use:


How this formula works

Working from the inside out, COUNTIF first gets a count of every value in B3:B11 in the range B3:B11. Because we supplying a range (array) of cells for the criteria, COUNTIF returns an array of counts as a result. In the example shown this array looks like this:


Next 1 is subtracted, which yields an array like this:


Note that every 1 in the array (i.e. items that appear just once) has been converted to a zero.

Next, SUMPRODUCT adds of the elements in this array and returns the result, which in this case is the number 2, which is then tested for a >0 value.

Any time a list contains duplicates, there will be at least two 1's in the array summed by SUMPRODUCT, so a final result of TRUE means the list contains duplicates.

Dave Bruns

Excel Formula Training

Learn key Excel formulas and functions with concise, clear videos. Master absolute/relative addresses, dates, text, named ranges, and tools for troubleshooting. Each video comes with a practice worksheet to follow along. Start today - instant access, 100% guarantee.

Click for more information.

This is fantastic. I am working on a report and this is exactly what I needed. Thanks. -Diane
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course