With the combination of **SUMPRODUCT** and **COUNTIF** function Excel allows us to check if there are exact matchable values in the list. This step by step tutorial will assist all levels of Excel users in checking if the list contains duplicates.

*Figure 1. Check duplicates in the list*

**Syntax of the SUMPRODUCT Formula**

**=SUMPRODUCT(array1, [array2], ...)**

The parameters of the SUMPRODUCT function are:

**array1**– an array of the values that need to be summed**[array2]**– an optional argument array for the function

**Syntax of the COUNTIF Formula**

** =COUNTIF(range,criteria)**

The parameters of the COUNTIF function are:

**range**– ranges where we want to apply our criteria**criteria**– a criteria in the range which we want to count.

**Setting up Our Data for Finding Duplicates in the List**

Our table consists of 2 columns: “Product ID” (column B) and “Amount” (column C). The idea is to check if there are duplicate values in the “Product ID” column and to return TRUE in the cell E3 if the list contains duplicates.

* Figure 2. Table structure for the example*

**Check Duplicates in the List with SUMPRODUCT and COUNTIF Functions**

We want to check if there are duplicate values in the column “Product ID” and to write TRUE in the cell E3 if there are matchings. In order to make the formula more clear, we will create a named range **Product_ID** for cell range B3:B7.

To create a named range we should follow the steps:

- Select the cell range that should be named
- Click on the name box in Excel
- Write the name for the cell range and press enter

* Figure 3. Creating a named range Product_ID for column “Product ID”*

The formula looks like:

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

The **array1** in the SUMPRODUCT function is the formula ** COUNTIF(Product_ID,Product_ID)-1**. The parameters

**range**and

**criteria**in the COUNTIF function are the named range Product_ID.

To apply the function we need to follow these steps:

- Select cell E3 and click on it
- Insert the formula:
`=SUMPRODUCT(COUNTIF(Product_ID,Product_ID)-1)>0`

- Press enter

*Figure 4. Check the duplicates in the list with the SUMPRODUCT and COUNTIF functions*

COUNTIF function counts the number of the occurrences of each item in the list, resulting in the array: {1;1;2;2;1}. This array is then subtracted with the number 1 changing the array to {0;0;1;1;0}. Result of the SUMPRODUCT function is 2, a sum of the array {0;0;1;1;0} items.

Formula part >0 is checking if the SUMPRODUCT result is greater than 0, retrieving TRUE if there are duplicates. If there are no duplicates in the list SUMPRODUCT output array will have all 0 items and formula output will be 0. Since 0 is not greater than 0, the final result will be FALSE.

Our list contains duplicates and SUMPRODUCT formula output is number 2. As a result, value TRUE is displayed in the cell E3.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment