Is there a formula to let me count if there are duplicates showing up in a row?
If I have 3 to 6 columns of individual numbers, I'd like to know if any numbers are duplicated in any particular row? To keep this simple, the best way to think of it is lottery numbers: If I have rows with 7-7-5; then 4-5-2 below that; then 1-5-7; then 2-2-5; then 1-0-9; then 1-1-3; then 1-0-1; then 5-4-2. You can see that the first, fourth, sixth and seventh row had duplicates.

Is there a formula that would calculate that for me?


You can use SUMPRODUCT for this, given 3 columns:

=IF(SUMPRODUCT((A1:C1<>"")/COUNTIF(A1:C1,A1:C1&""))<>3, "DUPES", "")

The SUMPRODUCT function here functions like an array formula without being one, and iterates through the entire range, and the result is the number of unique values. You then just compare that to the fixed number of columns you have, and assign TRUE/FALSE results as needed.

  • This is the correct way to do it. – Raystafarian Feb 24 '12 at 14:44

I'm not sure about a single formula. Since it's only 3-6 columns, just repeat this formula in the 3-6 cells next to your original data. (this example is for 3 columns)


Then if you really need it in one column... In the column beside that formula I just gave put an OR statement testing for > 1 for each column inside an IF statement.

=if(or(d1>1,e1>1, f1>1),"DUPLICATES!!","no dupes")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.