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