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 30-day!
ad select count duplicates

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words...
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum...
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns...
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Create Mailing List and Send Emails by Cell's Value...
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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.

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 tools for Excel. Full feature free trial 30-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:

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 tools for Excel. Full feature free trial 30-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, Kutools and Kutools Plus tabs are added by Kutools for Excel. If need it, please click for 30-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


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
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.
    Mel Scott · 3 months ago
    Thanks so much, saved me hours!
  • To post as a guest, your comment is unpublished.
    patel akshay · 6 months ago
    i need this
    There are 24 students.how many groups are there to take 10 student in 1 group
    example:
    1,2,3,4,5 take 1 group in 3 number

    ans={1,2,3} {2,3,4} {3,4,5} {4,5,1} {5,1,2,}

    how to do this excel will create this file
    • To post as a guest, your comment is unpublished.
      kellytte · 2 months ago
      Hi patel akshay,
      You can use the COMBIN function in Excel directly.
      =COMBIN(24,10)
  • To post as a guest, your comment is unpublished.
    Jothibasu · 9 months ago
    Thank You very much. It's very useful.
  • To post as a guest, your comment is unpublished.
    BillBrewster · 10 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 · 8 months 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.
  • To post as a guest, your comment is unpublished.
    rafiq · 2 years ago
    how can we count duplicate values in excel row
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Maybe you can copy the row to a column by the Transpose feature firstly?
  • To post as a guest, your comment is unpublished.
    Vishvas · 2 years ago
    Can't we get count of duplicate values with the help of count if function if yes how ? please advise
    as this is a interview question asked to me.
    • To post as a guest, your comment is unpublished.
      deepak · 1 years ago
      Vishvas use countif formula



      Forumula:=COUNTIF($A$2:$A$10,A1)
      row no A column Counts
      2 a 2
      3 b 2
      4 b 2
      5 c 1
      6 d 3
      7 d 3
      8 d 3
      9 a 2
      10 e 1
  • To post as a guest, your comment is unpublished.
    HARVIND · 2 years ago
    Hi , in below table some are appearing more than once, i need to catch them with number of appearance along with the series. like, B25 = 2. please help

    B25
    B17
    B9
    B15
    B1
    -
    B6
    B25
    B4
    B8
    B4
    B3
    B21
    B7
    B18
    B20
    B5
    B22
    B16
    B14
  • To post as a guest, your comment is unpublished.
    Bernadette Wright · 2 years ago
    Super helpful -- thank you!!
  • To post as a guest, your comment is unpublished.
    Unni · 3 years ago
    Hi,

    Please help me to solve the below problem

    =COUNTIFS('Weld Map'!$O$6:$O$7105,"="&F2,'Weld Map'!$V$6:$V$7105,"=Rej*",'Weld Map'!$AL$6:$AL$7105,"=REJ*")

    I need to count the text "REJ" from columns "V" and "AL" under the criteria of a period between F1 and F2
  • To post as a guest, your comment is unpublished.
    Unni · 3 years ago
    Hi,

    Please help me to solve the below problem

    =COUNTIFS('Weld Map'!$O$6:$O$7105,"="&F2,'Weld Map'!$V$6:$V$7105,"=Rej*",'Weld Map'!$AL$6:$AL$7105,"=REJ*")

    I need to count the text "REJ" from columns "V" and "AL" under the criteria of a period between F1 and F2

    Thanks and regards
    • To post as a guest, your comment is unpublished.
      aNKIT · 3 years ago
      use this instead of yours

      =SUM(COUNTIFS(J1:J196,"agree",A1:A196,"yes"),COUNTIFS(J1:J196,"agree",A1:A196,"no"))
  • To post as a guest, your comment is unpublished.
    Rhytha · 3 years ago
    I appreciate for the Solution provided. It is very helpful.
  • To post as a guest, your comment is unpublished.
    Najam Ul Hassan · 3 years ago
    very nice formula for counting of duplicate. It is very helpful
    Thanks extend office team
  • To post as a guest, your comment is unpublished.
    Stefan · 3 years ago
    Thank you SO much for this post its exactly what I needed!

    Please could you tell me why your formula "=COUNTIF($A$2:$A2,A2)" didn't work as expected in my worksheet until I amended it to "=COUNTIF($A2:$A$2;A2)" ?
    i.e. switching the absolute references around

    Thanks in advance
  • To post as a guest, your comment is unpublished.
    Khan · 3 years ago
    Please help to resolve this issue

    site ID Supplier Line
    12 abc good
    12 VV good
    12 TT good

    site ID Supplier Line
    12 abc good

    Required Supplier Name - formula required to show "Multiple Suppliers" as against same site iD and line there are 3 different suppliers.

    Please help to resolve this issue.
  • To post as a guest, your comment is unpublished.
    mohammad · 4 years ago
    many many thanks :-)
  • To post as a guest, your comment is unpublished.
    Ash · 4 years ago
    Let say I have different number of PO's in column A but some numbers are the same how can I count the total number of PO without including the duplicate number?
    • To post as a guest, your comment is unpublished.
      Rupesh Brahme · 2 years ago
      use formula =SUMPRODUCT(1/COUNTIF(A1:A1483, A1:A1483&""))
      =1/sumproduct(1/countif(range, criteria))
      :-)
  • To post as a guest, your comment is unpublished.
    Fujilives · 4 years ago
    =COUNTIF($A$1:$A1,A1)

    This method for finding duplicates is amazing, because it allows you to do a simple filter on the column (just deselect 0 and 1) to show all 'duplicate entries' instead of 'entries that have duplicates'. What I mean by this, is I can then select ALL visible after the filter, and delete the entire rows, and be left with only a single entry of a row containing that item.

    For MANY projects, this is a fantastic way to filter things down quickly.
  • To post as a guest, your comment is unpublished.
    Brice · 4 years ago
    If you want to get a sum of duplicate values in a column(without counting the first one), try:
    =IF(COUNTIF($A$1:$A1,A1)-1>=1,1,0)

    For example, let's say that you have a same value 5 times. It will count 1 for each of the 4 duplicate values. Then, you just have to get a sum.
  • To post as a guest, your comment is unpublished.
    Amol · 4 years ago
    suppose there is a column which contains values as GR1, GR2, GR3 and so on..... but some also getting repeated again. how can i get the final count of the item. Like if it reaches to GR29, the the value should show as 29 in the formula cell
  • To post as a guest, your comment is unpublished.
    Harrison · 4 years ago
    I am trying to label an individual data point as "1"...and if it has a duplicate, it will label the duplicates as "0"...but it would still label at least one of the data points as "1". Example, I could have one PO number on a truck, or multiple.

    Thanks
  • To post as a guest, your comment is unpublished.
    NAVEEN · 4 years ago
    i had query regarding for eg: 1st sheet of work book 1st is column is with data received fruits, 2nd column is fruits names, 3rd column is for normal defects, 4th column is for Major Defects, 5th column is for Critical defects

    2nd sheet for Normal Defects,
    3rd sheet for Major Defects,
    4th sheet for Critical Defects,
    My query is when we are updating these above sheet it total count should be reflected in 1st by individual fruits and for individual defects.

    Regards,
    Naveen kumar
  • To post as a guest, your comment is unpublished.
    NAVEEN · 4 years ago
    hI

    In sheet1 we have three columns, 1st columns "fruits names" 2nd column Date of received, Name of supplies only to supplies and in 3rd 4th 5th columns are Normal defect, Major defects and Critical defects,all these in 1st sheet.

    in 2nd sheet, 3rd sheet, 4th sheet, saparetly post all of Normal defects in 2nd sheet, Major defects in 3rd sheet, critical defects in 4th sheet. when we are updating these sheet it automatically should update 1st individual in normal, Major, Critical.

    Thanks
    Naveen
  • To post as a guest, your comment is unpublished.
    Ajeet singh · 4 years ago
    Impotent work if duplicate value.
  • To post as a guest, your comment is unpublished.
    Gaurav Pahuja · 5 years ago
    easy to use and helpful..:-p
  • To post as a guest, your comment is unpublished.
    munish · 5 years ago
    easy and helpful in large working
  • To post as a guest, your comment is unpublished.
    Zana · 5 years ago
    Awesome, it's easy and useful. Thaks
  • To post as a guest, your comment is unpublished.
    Priyanka · 5 years ago
    Is there any other function or way to calculate the same..?? Because countif() slows down the functioning of the sheet. Please suggest.
    • To post as a guest, your comment is unpublished.
      Ankit · 4 years ago
      contact me @@ if u want stop duplicacy!! * conditional formating duplicate
    • To post as a guest, your comment is unpublished.
      Amol Chopade · 5 years ago
      More use of functions and formulas make worksheet slower. There is no method to solve it even i suggest you to use special paste option after using formulas and functions. It will solve your problem. Just use it "Alt+s+e+v". :-)
  • To post as a guest, your comment is unpublished.
    Adnan Khan · 5 years ago
    easy and good one :D