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.
All articles Count duplicates in Excel How to use the duplicate count Excel Function – Excelchat

How to use the duplicate count Excel Function – Excelchat

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;

  1. 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).

  1. 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.

  1. 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;

  1. 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.

  1. 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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc