QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Mobile
  • Email
    • Gmail
  • Wiki

How to Count Duplicate Values in Excel?

By Neha T Leave a Comment

Are you trying to count duplicate values in Excel? Sometimes we compile multiple worksheets into in single large excel sheet. This integrated single worksheet may consist of duplicate values.

You may require to count the duplicate values in an excel sheet in order to get a clear view of the large data. The Excel formula COUNTIF() will play a most important role in counting the duplicate in the excel sheet. We have briefly discussed this COUNTIF() function in our previous content.

In this context, we will discuss a few methods to count duplicate values in excel.

  1. Count Duplicates
    • with 1st occurrence
    • without 1st occurrence
  2. Count Case-Sensitive Duplicates in Excel
  3. Count Duplicate Rows in Excel

Steps Count Duplicate Values in Excel:

Count Duplicates

Count Duplicates With 1st Occurrence

In the example below we have data in two columns one column has fruit items and the other has the quantity of each. With the COUNTIF() function implemented in the E column, we are able to count the frequency of duplicate fruit items in column B.

Cunt Duplicate Value in Excel -1

Count Duplicates Without 1st Occurrence

To count the frequency of duplicates excluding the first occurrence we just have reduced the count by one:

Cunt Duplicate Value in Excel -2

You can notice the difference between the count of duplicates with and without the first occurrence.

Count Case-Sensitive Duplicates in Excel

The COUNTIF function that we have used till now is case-insensitive. To count case-sensitive duplicates we can use the two formulas of Excel in collaboration. The two formulae that we will use are:

  1. EXACT: This formula returns TRUE if both the strings provided to it are the same and False if the strings are not the same. EXACT is case-sensitive which means even if the provided string is the same but one is in upper case and the other in lower case then it will return FALSE declaring the two strings are not the same.
  2. SUM: This formula returns the total count.

Note: To apply the formula as an array formula accurately press Ctrl + Shift + Enter while applying the formula to each cell.

Count Duplicate Value in Excel -3

Count Duplicate Rows in Excel

To count the number of duplicate rows in the excel sheet we will be using COUNTIFS function as you can see in the image below:

Count Duplicate Value in Excel -4

To exclude the first occurrence of a row we just have to reduce the result by one.

Count Duplicate Value in Excel -5

This is how you can count the occurrence of duplicate values in Excel.

You Might Also Like:

count unique values featured imageHow to Count Unique Values in Excel 2016? Delete Duplicates feature imageHow to Delete Duplicates in Excel 2016? feature imageHow to COUNTIF in MS Excel 2016? Highlight Duplicates in Excel-feature imageHow to Highlight Duplicates in Excel? compare two columns in Excel Feature imageHow to Compare Two Columns to Find Matches & Differences in Excel 2016? Calculate Average in Excel feature imageHow to Calculate Average in Excel? how-to-freeze-row-and-column-in-ms-excel-in-2016-featured-imageHow to Freeze Rows and Columns in MS Excel 2016? hide columns feature imageHow to Hide and Unhide Rows & Columns in Excel?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to Configure Location Tracking in Windows 10
  • How to Enter BIOS in Windows 10?
  • How to Upload and Download File in Google Drive?
  • How to Mute WhatsApp Group?
  • How to Create a Pivot Table in Excel?

Copyright © 2023 · QueHow · Contact Us