We can use COUNTIF to find duplicates in our spreadsheets.
Often times a spreadsheet may include duplicate data in a cell. row, or column. We can remove such duplicates without altering the data value itself by making use of Excel sum duplicates.
Figure 1. Count Duplicates in Excel
A very common task most people using spreadsheets in Excel perform is the analysis of data sets.
Sometimes , our worksheet may include some duplicates of data – meaning, a cell or row that is a duplicate of another cell or row. Especially when working with larger Excel worksheets or combining several smaller worksheets into a much bigger one.
When we want to eliminate those duplicate items without changing the information therein, we would like to know the amount of times a given value is duplicated within our set of data .
This article will show you how to count repeated values in Excel.
How to Count Duplicate Values in Excel
We can use one of Excel’s statistical functions – COUNTIF to find duplicates in our worksheet.
The COUNTIF duplicate operation in Excel works by returning the total amount of cells in a selected range that matches with a specified criteria.
We will now show you how find COUNTIF duplicates in Excel with the following straightforward steps;
- We begin by collecting our available data inside our spreadsheet:
Figure 2. Duplicate Data in Excel
Our goal here is to count duplicate values in Excel by using the COUNTIF duplicates function.
Be sure to leave an empty column of cells for returning our Excel duplicate count (column C above).
- Our Excel formula to count duplicates will be entered into cell C2 above:
=COUNTIF($A$2:$A$12,A2)
Figure 3. Find Duplicates in Excel
The COUNT IF duplicate function returned a value of 3, meaning that the product “Cucumber” was ordered 3 times as recorded in our spreadsheet records.
- Copy the modified version of our Excel formula to count duplicates down into the other cells in the OCCURRENCE column for the desired COUNT IF duplicate results:
Figure 4. COUNTIF Duplicates in Excel
If we want to count multiple values that are duplicated within our worksheet, we start by;
- Collecting our values for duplicate checking
Figure 5. Count Multiple Duplicates in Excel
Here, we can expand our Excel formula to count duplicates so that it can include multiple criteria.
- The COUNT IF duplicate formula we will enter into cell C2 below is as follows;
=COUNTIF(A2:A7,85)+COUNTIF(A2:A7,187)
Figure 6. Count Multiple Duplicates in Excel
Instant Connection to an Excel Expert
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