Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

# List Contains Duplicates

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.

### Did this post not answer your question? Get a solution from connecting with the expert.

Solution examples
i have a table with Dates, First names , Last names and i want to count how many times a name occurs, but if a name occurs more than once on a particular date i need to only count it once. this is on excel and not google sheets
Solved by F. H. in 31 mins
Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.
Solved by C. H. in 16 mins
If a cell in another sheet is populated I need a vlookup done. If the cell is not populated I need the cell to return blank.
Solved by T. D. in 60 mins
I am trying to make a chart that turns a week range red if nothing is entered in the range. If something is entered then I would like it to turn green. Please Help
Solved by E. U. in 43 mins
I need a check box to show/hide an answer of an if function
Solved by Z. U. in 23 mins