|Check out our Excel Resources|
If you want a single formula that just assesses whether or not duplicates exit in the target range, use:
It's the SUMPRODUCT and COUNTIF part that I'm not understanding. Could you "walk" me through the formula, inside out?
For example, I tried just the COUNTIF(A1:AJ1,A1:AJ1) and get a zero, whether there is a duplicate in the range or not. What does this piece do in your formula?
Then there is the next ">1" which I might understand if I knew what the countif is returning.
Then the next two pieces, the "+0" and the second ">1"; I don't see what they do.
That's right. It's exactly what it should do: COUNTIF can only count the occurence of a single value at a time.
SUMPRODUCT forces COUNTIF(A1:AJ1,A1:AJ1) to return an array of counts for each value in A1:AJ1, say,
What does this piece do in your formula?
The test COUNTIF(A1:AJ1,A1:AJ1)>1 is factualized as
which leads to another array, consisting of logical values:
+0 in (COUNTIF(A1:AJ1,A1:AJ1)>1)+0 forces/coerces Excel to treat the logical values as numbers where TRUE=1 and FALSE=0, so  becomes:
SUMPRODUCT, so to say, sums this array into a single numeric value, which is tested in the condition part.
The second 1 in >1 might be formulated as >0. A single duplicate will always produce a 2, that's why it doesn't matter much which you pick: >1 or >0.