Farihat
Microsoft Excel is the most useful data-tracking software. For datasets that have numerous worksheets or users or identity numbers, it is difficult to analyze at ease. In cases of data tracking, there are significant examples where duplicate data exists. Data analysts mostly evaluate this repetition to avoid data overlap. There are also cases where you may only need the total count that has duplicate values. So, we can say that, if you want to count duplicate values for multiple columns in Excel, it is a complex problem since there is no absolute formula to compare and count altogether.
This article can guide you to eight different techniques. All the methods have step-by-step instructions with a figurative description.
Following is an image where you can see the methods of how you can easily count duplicate values in multiple columns in Excel for a given dataset.
📁 Download Excel File
Download the Excel file below.
Learn to Count Duplicate Values in Multiple Columns in Excel with These 8 Approaches
This article is going to introduce eight approaches to count duplicate values in multiple columns in Excel. The methods will help you to understand the uses elaborately. The following discussion will cover the uses of the COUNTIF, SUMPRODUCT, MATCH, and SUM functions to directly count the duplicates. You will also get complex methods, you can use the AND, SUBTOTAL, and VLOOKUP functions. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.
1. Application of COUNTIF Function to Count Duplicate Values in Multiple Columns in Excel
The COUNTIF function is applicable for counting cell numbers.
COUNTIF(cell_range,criteria)
cell_range – cell range where you want to search from.
criteria – cell value or constant value which you want to count.
It counts the number of cells from the cell range that follows the criteria. We can use this method to directly count duplicate values in multiple columns in Excel. Although you need to input the data set manually, it is the only method where you can do the job using one function only.
⬇️⬇️ STEPS ⬇️⬇️
- First, create a table below the dataset with the data for counting reference. Here, we inserted the player names manually.
- Now, select cell C13 and enter the following formula.
🔨 Formula Breakdown
COUNTIF($B$6:$D$10,B13)
👉 Here, COUNTIF returns the cell numbers that have B13 data from B6:D10.
- Then, using the Fill Handle icon, copy the formula from C13 to C22.
- After that, select cell D13 and enter the following formula.
=COUNTIF(C13:C22,">1")🔨 Formula Breakdown
COUNTIF(C13:C22,”>1″)
👉 Here, COUNTIF returns the cell numbers that have values larger than 1 from C13:C22.
👉 COUNTIF(C13:C22,”>1″) adds both the number of cells and counts total duplicates.
Finally, you get 3 as output which resembles that the dataset has 3 duplicate inputs.
2. Combination of AND and COUNTIF Functions to Count Duplicates in Multiple Columns
The AND function is convenient to compare cell numbers for every column under the criteria. The COUNTIF function counts the output from the AND function.
AND(criteria1, criteria2, [criteria3], ..).
criteria1 – The first cell reference or criteria that you want to tag along with the next one(s).
criteria2 – The second cell reference or criteria that you want to add with the rest of the argument (s).
The AND function can bring together multiple cells as this is the only method that can confirm duplicate values for every column. For the dataset in this method, we have shown the use of the AND function for three columns. Although you can use this method for any number of columns. You need to understand that it only gives output true if there are duplicates in all of them. If we want to count duplicate values for multiple columns in Excel even if all the columns do not have duplicate values, we can use the OR function. This formula doesn’t need individually organized values beforehand like the first method.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell E6 and enter the following formula here.
=IF(AND(B6=C6,C6=D6,D6=B6), "Duplicate","No Duplicate")🔨 Formula Breakdown
IF(AND(B6=C6,C6=D6,D6=B6), “Duplicate”,”No Duplicate”)
👉 Here, AND returns TRUE if the data on B6, C6, and D6 all cells are same; FALSE otherwise.
👉 IF shows “Duplicate” for the return of AND as TRUE; “Not Duplicate” otherwise.
- Then, using the Fill Handle icon, copy the formula from E6 to E10.
- After that, select cell E11 and enter the following formula.
=COUNTIF(E6:E10,"Duplicate")🔨 Formula Breakdown
COUNTIF(E6:E10,”Duplicate”)
👉 Here, COUNTIF returns the cell numbers that have “Duplicate” data from E6:E10.
Finally, you get 1 as output which resembles that the dataset has 1 row that has the same inputs for all 3 columns.
N.B.: Even if row 7 has two “Pedri”, the method returns “No Duplicate”. It occurs since the AND function returns “Duplicate” only when all three columns have the same value. To get a “Duplicate” return we can use the following formula.
=IF(OR(B6=C6,C6=D6,D6=B6), "Duplicate","No Duplicate")3. Combine SUMPRODUCT and COUNTIF Functions to Determine Duplicate Values in Multiple Columns
The SUMPRODUCT function returns the sum of the specific ranges’ products. You can do addition, subtraction, and division apart from multiplication as well.
SUMPRODUCT(array1, [array2], ..)
array1 – Here you add the first array argument with the data that you want to multiply and then add.
array2 – It is for the same contribution as array1 and is optional for use for the function.
If you combine the SUMPRODUCT and the COUNTIF functions, you can easily determine total duplicate values in multiple columns in Excel. The formula counts the duplicate values first. Then, the SUMPRODUCT eventually counts the total duplicate values in multiple columns. Unlike, the previous two methods, this method is applicable to two columns only and can count the number directly.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, select cell D6 and enter the following formula here.
🔨 Formula Breakdown
SUMPRODUCT(COUNTIF($B$6:$B$10,$C$6:$C$10))
👉 Here, COUNTIF returns the cell numbers that have C6:C10 data from B6:B10.
👉 Also, SUMPRODUCT adds and multiplies returns from the COUNTIF function.
Therefore, we get 2 since there are Sergi and Pedri from two columns as duplicates.
📕 Read More: 7 Ways to Count Duplicates in Two Columns in Excel
4. Excel MATCH Function to Count Duplicate Values
The MATCH function searches for a declared value in a cell range and returns its position in the range.
MATCH(lookup_value, lookup_array, [match_type])
lookup_value – This is usually the cell reference or absolute value that should match with the lookup_array.
lookup_array – This argument takes a cell range, where the value is searched.
match_type – Although an optional part, helps in case of relative matches by using 0 as an argument, 1 otherwise as default i.e. exact match.
The MATCH function matches the data of one column to another and gives an exact or relative match. From the position return, you can use the ISNUMBER function to make sure the position is valid. Finally, the SUMPRODUCT function will help you to count the total duplicate values in multiple columns in Excel. This method works almost as efficiently as the third method.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell D6 and enter the following formula here.
🔨 Formula Breakdown
SUMPRODUCT(–(ISNUMBER(MATCH(B6:B10,C6:C10,0))))
👉 Here, MATCH looks for B6:B10 data range from data range C6 to C10. It returns the search value.
👉 Also, ISNUMBER returns only the number values from the output of the MATCH function.
👉 Finally, SUMPRODUCT adds and multiplies returns from the ISNUMBER function.
Moreover, the result comes out as 2 since the formula gets two sets of data as duplicates.
5. Merging SUM and MATCH Functions to Count Duplicate Values in Multiple Columns
The SUM function adds individual values, cell references, ranges, or a mix of all three. The MATCH function searches for data or criteria for a specific cell range.
SUM(number1,[number2],…)
number1 – Here, you should use a cell reference or cell range to do the sum operation.
number2 – This argument is optional and helpful when non-adjacent cells are up for summation.
The MATCH function provides the position of data and the ISNA function confirms the validity of the position. Here, you can use the IF function to apply conditions and how you want to get the data. Finally, the SUM function adds all the returns from the IF function formula. Although this method uses four functions, it is easier to understand the structure than the previous method. As a result, if you have a complex dataset, this method can help you to successfully modify and count duplicate values.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell D6 and enter the following formula here.
🔨 Formula Breakdown
SUM(IF(ISNA(MATCH(B6:B10,C6:C10,0)),0,1))
👉 Here, MATCH looks for B6:B10 data range from data range C6 to C10. It returns the exact match value.
👉 Also, ISNA returns FALSE if there is no error. In case of an error, it returns TRUE.
👉 Again, IF returns 0 or 1 according to the condition from the ISNA function.
👉 Finally, SUM adds returns from the IF function.
Finally, you can see in the image that the output is 2.
6. VLOOKUP Functionto Count Duplicate Values in Multiple Columns
The VLOOKUP function can find things in a table or a range by row. It can work across different worksheets as well.
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
lookup_value – You should add the cell reference or absolute data that you need to look for.
table_array – The lookup_value must be present within this cell range or it will show an error.
col_index_num – A specified column number should be here within the range where the value will return.
range_lookup – It returns an approximate or exact match according to commands as 1/TRUE or 0/FALSE.
As we discussed above regarding the VLOOKUP function, it looks for cell values as the formula. The ISERROR function provides “FALSE” if any duplicate is found and “TRUTH” otherwise. Since our motive is to count the duplicate values in multiple columns, we can just count the “FALSE” returns under the “Not Duplicates” column. Now, the only confusion in this method causes due to the ISERROR functionality. Apart from that we can modify the VLOOKUP function criteria and range into a more diverse and efficient formula to count duplicate values in Excel for multiple columns using this method.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cell D6 and enter the following formula here.
🔨 Formula Breakdown
ISERROR(VLOOKUP(B6,$C$6:$C$10,1,0))
👉 Here, VLOOKUP looks for B6 from cell range C6:C10.
👉 Furthermore, ISERROR refers to the error value.
- Next, copy the formulas down their cells using the Fill Handle icon.
- Next, select cell E6 and enter the following formula here.
=COUNTIF(D6:D10,"FALSE")🔨 Formula Breakdown
COUNTIF(D6:D10,“FALSE”)
👉 Here, COUNTIF returns the cell numbers that have “FALSE” data from D6:D10.
Therefore, you should get the count output as 2 since there are two sets of duplicate values in multiple columns in the Excel file.
📕 Read More: 3 Ways to Count Duplicate Values in Excel Only Once
7. Apply Filter and SUBTOTAL Function
The Filter option is one of the most outstanding tools in Microsoft Excel. On the other hand, the SUBTOTAL function is an automatic update addition function that aligns with our mission to count duplicate values in multiple cells in Excel.
SUBTOTAL(function_num,ref1,[ref2],…)
function_num – It varies according to the function required for the references. For example, we have used 3 to represent the COUNTA function.
ref1 – This argument belongs to the cell range where you want the previous function to be in action.
ref2 – This argument is optional and denotes cell references as well.
Before using the Filter tool, you can use a formula to declare which of the rows provide duplicate values. For that, you can merge the IF, ISNUMBER, and MATCH functions. We discussed functionality for most of these functions in the previous methods. The returns can work under the Filter tool and the SUBTOTAL function will auto-update its result for the filtered dataset. This method is useful for large datasets and easy to update any time you want without modifying the formula like the previous methods.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, select cell D6 and enter the following formula here.
=IF(ISNUMBER(MATCH(B6,$C$6:$C$10,0)),"Duplicate","Unique")🔨 Formula Breakdown
IF(ISNUMBER(MATCH(B6,$C$6:$C$10,0)),”Duplicate”,”Unique”)
👉 Here, MATCH looks for B6 data range from data range C6 to C10. It returns the exact match value.
👉 Also, ISNUMBER returns the number values from the MATCH function.
👉 Again, IF returns “Duplicate” or “Unique” according to the condition from the ISNUMBER function.
- Next, copy the formulas down their cells using the Fill Handle icon.
- After that, select cell E6 and enter the following formula here.
🔨 Formula Breakdown
SUBTOTAL(3,D6:D10)
👉 Here, SUBTOTAL returns the count value by using 3 as the COUNTA function from data range D6 to D10.
- Now, select cell range D5:D10.
- Here, choose the Filter option from the Sort & Filter drop-down, under the Editing group of the Home tab.
- Here, click on the drop-down icon available beside the column header like the following image.
- Now, uncheck the Unique option from the Filter drop-down.
- After that, hit the OK button.
Moreover, you get the total count for the duplicate values for multiple columns in Excel.
8. Count Duplicate Values in Multiple Columns by Using UNIQUE Function in Microsoft Office 365
For users of previous versions of Microsoft Excel, this method isn’t applicable since it is only possible for Microsoft Office 365 and for a few other latest versions for now. Now, the UNIQUE function returns a range of unique values for a specific dataset just as it were.
UNIQUE(array,[by_col],[exactly_once])
array – That is for the source cell range, from where you will extract the unique values.
by_col – This argument is optional whereas TRUTH means to compare columns against columns and FALSE means to compare rows against rows.
exactly_once – This optional argument needs TRUTH in its syntax if you want to consider duplicate values once, otherwise FALSE to consider them each of the times.
The UNIQUE function is helpful to generate a table with unique values. Now, to navigate it to count duplicate values in multiple columns you need to incorporate it with the CHOOSE, INDEX, and COUNTIFS functions. These will help you to distribute, position, and count respectively. No other methods can generate a new table and declare if duplicate or unique. After that, you can simply use the COUNTIF function to count the total duplicate values in multiple columns in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell E6 and enter the following formula.
🔨 Formula Breakdown
CHOOSE({1,2,3},UNIQUE(B6:C10),INDEX(UNIQUE(B6:C10),0,2),COUNTIFS(B6:C10,INDEX(UNIQUE(B6:C10),0,1),B6:C10,INDEX(UNIQUE(B6:C10),0,2)))
👉 Here, UNIQUE(B6:C10) returns all the unique cells present within cell range B6:C10.
👉 INDEX(UNIQUE(B6:C10),0,1) and INDEX(UNIQUE(B6:C10),0,2) calls the returns of UNIQUE function. The formula is done by Columns E and F respectively.
👉 COUNTIFS counts cells if the condition from the INDEX function checks out for another column as a duplicate for range B6:C10.
👉 Finally, CHOOSE distributes column IDs for placing the data.
- As visible from the picture, the entire dataset and duplicate countings are automatically available here.
- Now, to count the total duplicates, select cell G11 and enter the following formula.
🔨 Formula Breakdown
COUNTIF(G6:G10,2)
👉 Here, COUNTIF returns the cell numbers that have 2 data from G6:G10.
Finally, you will be able to count duplicate values for multiple columns in Excel using this method.
Use of Conditional Formatting to Highlight Duplicate Values in Multiple Columns
The Conditional Formatting option is one of the easiest tools to showcase data or cells fulfilling a specific criterion. Here, we will share how you can highlight all the duplicate values. Visually, it gets easy for users to understand the presence of duplicates. You can even count them using the Find tool for fewer sets of columns. You can even modify the cell format with their fill, font, border, etc. to highlight duplicate values.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select the cell range B6:D10.
- Now, select the Duplicate Values option from the Highlight Cells Rules menu under the Conditional Formatting drop-down of the Home tab.
- Here, you will get the Duplicate Values option.
- Then, you can modify the format if you wish from the values with drop-down.
- Next, process with the OK button.
Therefore, you should get an output with the Light Red Fill format and get the table output like the following picture.
How to Count Duplicates for Multiple Worksheets
There are cases where you need to generate data across multiple sheets. Now, if your job is to count duplicate values for multiple columns in Excel for different worksheets, you can follow this method with ease. This method includes a formula with the IF, ISERROR, and VLOOKUP functions to declare the duplicate and unique values. The return is eventually useful for the COUNTIF function to add the total count.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, create a dataset for the “2022-2023” worksheet where the first column is available.
- Next, select cell C6 from the “2021-2022” worksheet with the second column and enter the following formula.
=IF(ISERROR(VLOOKUP(B6,'2022-2023'!$B$6:$B$10,1,0)),"No","Yes")🔨 Formula Breakdown
IF(ISERROR(VLOOKUP(B6,’2022-2023′!$B$6:$B$10,1,0)),”No”,”Yes”)
👉 Here, VLOOKUP looks for B6 from cell range B6:B10 of worksheet “2022-2023”. The return gets to the ISERROR function.
👉 Furthermore, ISERROR refers to the error value.
👉 Again, IF returns “No” or “Yes” according to the condition from the ISERROR function.
- Here, copy the formulas down their cells using the Fill Handle icon.
- After that, select cell D6 and enter the following formula here.
=COUNTIF(C6:C10,"Yes")🔨 Formula Breakdown
COUNTIF(C6:C10,“Yes”)
👉 Here, COUNTIF returns the cell numbers that have “Yes” data from C6:C10.
Finally, you can count duplicate values for multiple columns across the sheets in Excel.
📄 Important Notes
🖊️ This article has multiple methods to count duplicate values for two and more than two columns separately. Apart from methods 1 and 2, you can’t use other methods for more than two columns in Excel.
🖊️ Many of the methods are available to count duplicate values in two steps and 3 methods to count automatically.
📝 Takeaways from This Article
The article lets the readers understand the variety of options available to count duplicate values in multiple columns in Excel.
📌 Primarily, you can count duplicates in two columns using the COUNTIF and SUM functions in Excel.
📌 You can combine the AND and COUNTIF functions to count duplicate values in all of the multiple columns in Excel. It is the only method that shows Duplicate when all the corresponding columns have duplicate values.
📌 Also, you can use the SUMPRODUCT function with the COUNTIF function to count duplicate values in multiple columns in Excel. You can directly get the total number of duplicates.
📌 You can employ MATCH and ISNUMBER functions as well.
📌 Then, you can count duplicates in two columns using the SUM and MATCH functions in Excel. You should add IF and ISNA functions with it for better functionality.
📌 You can employ the VLOOKUP function as well.
📌 Also, you can use the Filter option with the SUBTOTAL function to count duplicates in two columns in Excel. You can directly get the total number of duplicates.
📌 Here, Microsoft Office 365 can also count duplicate values in multiple columns using the UNIQUE function.
Conclusion
To count duplicate values, eight methods are available. You can use the COUNTIF, SUMPRODUCT, MATCH, and SUM functions to directly count the duplicates. For step-by-step complex methods, you can use the AND, SUBTOTAL, and VLOOKUP functions. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.
