Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to count duplicate values in a column in Excel?

If you have a list of data in a worksheet with unique values and duplicate values, and you do not only want to count the frequency of duplicate values also want to know the order of the occurrence of the duplicate values. In Excel, the COUNTIF function can help you count the duplicate values.

Count the frequency of duplicates in Excel

Count the order of occurrence of duplicates in Excel

Count and Select all duplicate values in a column with Kutools for Excel

Count occurrences of each duplicate in a column with Kutools for Excel

Easily count and select all duplicate values in a column with Kutools for Excel

Provided by Kutools for Excel. Full Feature Free Trial 60-day!
ad select count duplicates


arrow blue right bubble Count the frequency of duplicates in Excel

In Excel, you can use COUNTIF function to count the duplicates.

Select a blank cell adjacent to the first data of your list, and type this formula =COUNTIF($A$2:$A$9, A2) (the range $A$2:$A$9 indicates the list of data, and A2 stands the cell you want to count the frequency, you can change them as you need), then press Enter, and drag the fill handle to fill the column you need. See screenshot:

Tip: If you want to count the duplicates in the whole Column, use this formula =COUNTIF(A:A, A2) (the Column A indicates column of data, and A2 stands the cell you want to count the frequency, you can change them as you need).


arrow blue right bubble Count the order of occurrence of duplicates in Excel

But if you want to count the order of the occurrence of the duplicates, you can use the following formula.

Select a blank cell adjacent to the first data of your list, and type this formula =COUNTIF($A$2:$A2,A2) (the range $A$2:$A2 indicates the list of data, and A2 stands the cell you want to count the order, you can change them as you need), then press Enter, and drag the fill handle to fill the column you need. See screenshot:


arrow blue right bubble Count and select all duplicates in a column with Kutools for Excel

Sometimes you may want to count and select all duplicates in a specified column. You can get it done easily with Kutools for Excel’s Select Duplicates & Unique Cells utility.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Select the column or list that you will count all duplicates, and click the Kutools > Select > Select Duplicates & Unique Cells.

2. In the opening Select Duplicate & Unique Cells dialog box, check the Duplicates (Except 1st one) option or All duplicates (Including 1st one) option as you need, and click the Ok button.

And then you will see a dialog box comes out and shows how many duplicates are selected, and at the same time duplicates are selected in the specified column.

Note: If you want to count all duplicates including the first one, you need to check the All duplicates (Including 1st one) option in the Select Duplicate & Unique Cells dialog box.

3. Click the OK button.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


arrow blue right bubble Count occurrences of each duplicate in a column with Kutools for Excel

Kutools for Excel’s Advanced Combine Rows utility can help Excel users to batch count the occurrences of each items in a column (the Fruited Column in our case), and then delete the duplicate rows based on this column (the Fruit Column) easily as below:

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Select the table containing the column where you will count each duplicate, and click Kutools > Content > Advanced Combine Rows.

2. In the Advanced Combine Rows, select the column you will count each duplicate and click Primary Key, next select the column you will put counting results in and click Calculate > Count, and then click the OK button. See screenshot:

And now it has counted the occurrence of each duplicate in the specified column. See screenshot:

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


arrow blue right bubbleDemo: count duplicate values in a column in Excel by Kutools for Excel

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


arrow blue right bubbleRelative Articles:

Count merged cells in Excel

Count blank cells or nonblank cells in a range in Excel



Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jothibasu · 28 days ago
    Thank You very much. It's very useful.
  • To post as a guest, your comment is unpublished.
    BillBrewster · 2 months ago
    I must be stupid as the KuTools solution is not working for me. I have copied the example with Fruits column (which I put 3 Apple entries in), made a column next to it which is blank called Count column. KuTools -> Content -> Advanced Combine Rows. Select Fruit column as primary key, Count column as Calculate -> Count. Hit OK. Nothing. No change to the sheet. Help please?
    • To post as a guest, your comment is unpublished.
      kellytte · 20 days ago
      Hi BillBrewster,
      Would you do me a favor and send me a screenshot about the Advanced Combine Rows dialog? Just like the screenshot as below. Thanks in advance!
  • To post as a guest, your comment is unpublished.
    Muhammad Husnain · 1 years ago
    Dear, I am working on the attached Screenshot and Excel File. I need to calculate the Values in Column "G" i.e. to Count unique text values based on multiple (Two) criteria, but criteria are in NUMBERS. Further, Its is big sheet, therefore I want to use the cell reference in range and in criteria. I am very confused. I want to count the column C i.e. Degree based on the Criteria E and F. That is,I need to set formula in G2, such that, Look E2 in column A and Look F2 in column B and count the unique text values in Column C. Hope it is clear.

    I am very thankful to you please


    Details:
    I have a data of more than 1000 companies with different year. As in column A, I have companies and B shows the year for which these companies have data. For example, First company have data for year 2011, 2012, 2013 and 2015. For company 2, I have data for year 2014 and 2011 and so on. Since majority of companies have data from 2011 to 2015, therefore I select this range in column F for each company. Now, I want to count the unique Degrees for cell G2, if there is 389841988 (Company ID for First company) in A and there is 2011 in Column B. Now I need to set formula in G2,in a way, If I will drag the formula G2 in cell G3, then It should give me the value by looking such as if there is 389841988 (Company ID for First company) in A and there is 2012 in Column B, the based on these two criteria, it should count the unique text in C2 and so on.
    Look on the second sheet named Example, I did the same for INDEX, MATCH function and as well for SUMIFS function; in C2 I have the array formula INDEX($F:$H,MATCH(1,(A2=$F:$F)*(B2=$G:$G),0),3) and in D2 I have SUMIFS(I:I,F:F,A2,G:G,B2). These help me to do what I want with two criteria (To match and also to sum) and also I can drag these for rest of cell. I am looking something like this, or anything that I can drag to count unique degrees. Thank you very much. I am waiting.

    Plz help. I am very thankful to for this kindness
  • To post as a guest, your comment is unpublished.
    Muhammad Husnain · 1 years ago
    Dear, I got the way to upload the screenshot. Kindly consider the attached one,
    Thank you very much
    • To post as a guest, your comment is unpublished.
      Muhammad Husnain · 1 years ago
      Dear, I am working on the attached Screenshot and Excel File. I need to calculate the Values in Column "G" i.e. to Count unique text values based on multiple (Two) criteria, but criteria are in NUMBERS. Further, Its is big sheet, therefore I want to use the cell reference in range and in criteria. I am very confused. I want to count the column C i.e. Degree based on the Criteria E and F. That is,I need to set formula in G2, such that, Look E2 in column A and Look F2 in column B and count the unique text values in Column C. Hope it is clear.

      I am very thankful to you please


      Details:
      I have a data of more than 1000 companies with different year. As in column A, I have companies and B shows the year for which these companies have data. For example, First company have data for year 2011, 2012, 2013 and 2015. For company 2, I have data for year 2014 and 2011 and so on. Since majority of companies have data from 2011 to 2015, therefore I select this range in column F for each company. Now, I want to count the unique Degrees for cell G2, if there is 389841988 (Company ID for First company) in A and there is 2011 in Column B. Now I need to set formula in G2,in a way, If I will drag the formula G2 in cell G3, then It should give me the value by looking such as if there is 389841988 (Company ID for First company) in A and there is 2012 in Column B, the based on these two criteria, it should count the unique text in C2 and so on.
      Look on the second sheet named Example, I did the same for INDEX, MATCH function and as well for SUMIFS function; in C2 I have the array formula INDEX($F:$H,MATCH(1,(A2=$F:$F)*(B2=$G:$G),0),3) and in D2 I have SUMIFS(I:I,F:F,A2,G:G,B2). These help me to do what I want with two criteria (To match and also to sum) and also I can drag these for rest of cell. I am looking something like this, or anything that I can drag to count unique degrees. Thank you very much. I am waiting.

      Plz help. I am very thankful to for this kindness
    • To post as a guest, your comment is unpublished.
      Muhammad Husnain · 1 years ago
      The screenshot is not showing in the post. I don't know way.
  • To post as a guest, your comment is unpublished.
    Muhammad Husnain · 1 years ago
    Dear, I am working on the attached Sheet. I need to calculate the Values in Column "G" i.e. to Count unique text values based on multiple (Two) criteria, but criteria are in NUMBERS not in TEXT. Further, Its is big sheet, therefore I want to use the cell reference in range and in criteria. I am very confused. I want to count the column C i.e. Degree based on the Criteria E and F. That is, Look E2 in column A and Look F2 in column B and count the unique text values in Column C. Hope it is clear.
    Plz help. Thank you
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi,
      Could you upload a screenshot about your problem? A picture may help us understand you problem much clear. Thank you!
      • To post as a guest, your comment is unpublished.
        Husnain · 1 years ago
        thank you very much.
        I am trying to upload the Screenshot, but I don't know how I can upload it in the comment section.


        CompanyID* YEAR Degree CompanyID* YEAR Count Unique Degree
        389841988 2015 PhD 389841988 2011 1
        389841988 2015 Master 389841988 2012 2
        389841988 2011 Matric 389841988 2013 1
        389841988 2012 PhD 389841988 2014 0
        389841988 2012 PhD 389841988 2015 2
        389841988 2012 Matric 23819116896 2011 1
        389841988 2013 Matric 23819116896 2012 0
        23819116896 2014 Master 23819116896 2013 0
        23819116896 2014 Master 23819116896 2014 1
        23819116896 2011 Master 23819116896 2015 0
        168402710018 2011 Master 168402710018 2011 1
        168402710018 2014 PhD 168402710018 2012 0
        168402710018 2014 PhD 168402710018 2013 0
        168402710018 2014 1
        168402710018 2015 0
        • To post as a guest, your comment is unpublished.
          deepak · 1 years ago
          Row Number B column c column D column E column F column G column

          Forumula: =C1&D1&E1&F1&G1

          13 168402710018 2014 PhD 1.68403E+11 2012 0 2014PhD16840271001820120
          14 168402710018 2014 PhD 1.68403E+11 2012 0 2014PhD16840271001820120


          Result: Row number 13 and 14 are same.