Are you anxious to know if a range or list contains duplicates or not? Here is an adequate guideline for you to run specific formulas on your range and check it.

**FORMULA**

The generic formula to check if a range of cell contains the duplicate value or not is:

**=SUMPRODUCT(COUNTIF(list,list)-1)>0**

OR

**=SUMPRODUCT(COUNTIF(A2:A20,A2:A20)-1)>0**

OR

**=SUMPRODUCT((COUNTIF(list,list)-1)*(list<>""))>0**

Where **the list** has named a range of cell range A2:A20 as per above formula syntax

**Explanation**

This is quite a simple formula which you need to use in smooth effective steps to check if a range contains any duplicates. Let’s see in detail how this formula actually works.

First, the COUNTIF formula is applied which analyzes each value in the range for you. The array of range or list that we provide is taken up as input criteria by the COUNTIF and then it gives out the result in the form of an array of counts. The array appears in the form of ones and two’s but after the subtraction of 1, appears in the form of zeroes and ones. Then the application of SUMPRODUCT on this array signifies the duplicates in the form of TRUE or FALSE.

In order to remove any chances of errors, you can use this alternative for the sake of increasing the reliability of your tests:

**=SUMPRODUCT((COUNTIF(list,list)-1)*(list<>""))>0**

This converts into 0 all the blank spaces which might cause any errors in the result.

So, by using this simple formula one can easily check the list contains duplicates to perform the error-free calculation.

**Example**

In this example, we made use of the COUNTIF formula in combination with the SUMPRODUCT formula. The formula that we used in the below example is the following:

**=SUMPRODUCT((COUNTIF(list,list)-1)*(list<>""))>0**

Where the list has named a range of cell range **B3:B11**. Here is how you can check whether a range or list contains duplicates or not as per below image.

*Figure 1. Checking the list contains duplicates by a simple formula*

## Leave a Comment