Go Back

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.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I have list of items which contains duplicates. I need a formula to extract unique items from that list
Solved by S. Q. in 25 mins
i have a file with datas which contains duplicates, and i need to exclude the duplicates by keeping the recent data by application number within the duplicates
Solved by C. U. in 27 mins
I am trying to form a list of duplicates in a large spreadsheet. I have already done conditional formatting to highlight duplicates in two columns but cannot extract the duplicates to a separate list.
Solved by Z. H. in 30 mins

Leave a Comment

avatar