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