Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe dubbele waarden in een kolom in Excel te tellen?

Als u een lijst met gegevens in een werkblad met unieke waarden en dubbele waarden heeft en u niet alleen de frequentie van dubbele waarden wilt tellen, wilt u ook weten in welke volgorde de dubbele waarden voorkomen. In Excel kan de AANTAL.ALS-functie u helpen de dubbele waarden te tellen.

Tel de frequentie van duplicaten in Excel

Tel de volgorde van voorkomen van duplicaten in Excel

Tel en selecteer alle dubbele waarden in een kolom met Kutools voor Excel

Aantal keer voorkomen van elk duplicaat in een kolom met Kutools voor Excel

Tel eenvoudig en selecteer alle dubbele waarden in een kolom met Kutools voor Excel

Geleverd door Kutools voor Excel. Volledige functie Gratis proef 60-dag!
ad select count duplicates

Tabblad Office Schakel bewerken en browsen met tabbladen in Office in en maak uw werk veel eenvoudiger ...
Kutools voor Excel brengt 300 geavanceerde functies naar Excel en verhoogt uw productiviteit met 80%
  • Auto-tekst: Maak uw favoriete grafieken, afbeeldingen, cellen, complexe formules en hergebruiken ze snel in de toekomst.
  • Meer dan 20-tekstfuncties: Nummer uit tekststring halen; Een deel van de tekst extraheren of verwijderen; Nummers en valuta's omzetten in Engelse woorden ...
  • Tools samenvoegen: Meerdere werkmappen en bladen in één; Meerdere cellen / rijen / kolommen samenvoegen en gegevens bewaren; Dubbele rijen en som samenvoegen ...
  • Split gereedschap: Gegevens splitsen in meerdere bladen op basis van waarde; Eén werkmap naar meerdere Excel-, PDF- of CSV-bestanden; Eén kolom naar meerdere kolommen ...
  • Plakken overslaan Verborgen / gefilterde rijen; Tel en som op achtergrondkleur; Maak een verzendlijst en Verzend e-mails op waarde van Cell...
  • Super filter: Maak geavanceerde filterschema's en pas deze toe op alle bladen; Soort per week, dag, frequentie en meer; filters door vetgedrukt, formules, commentaar ...
  • Meer dan 300 krachtige functies; Werkt met Office 2007-2019 en 365; Ondersteunt alle talen; Eenvoudig inzetbaar in bedrijf; Volledige functionaliteit 60-daagse gratis proefversie.

pijl blauwe rechterbel Tel de frequentie van duplicaten in Excel

In Excel kunt u de functie AANTAL.ALS gebruiken om de duplicaten te tellen.

Selecteer een lege cel naast de eerste gegevens in uw lijst en typ deze formule = AANTAL.ALS ($ A $ 2: $ A $ 9, A2) (het bereik $ A $ 2: $ A $ 9 geeft de lijst met gegevens aan, en A2 staat de cel waarvan u de frequentie wilt tellen, u kunt deze naar wens wijzigen) en druk vervolgens op invoerenen sleep de vulgreep om de kolom te vullen die u nodig hebt. Zie screenshot:

Tip: Gebruik deze formule als u de duplicaten in de gehele kolom wilt tellen = AANTAL.ALS (A: A, A2) (De Kolom A geeft kolom met gegevens aan, en A2 staat de cel waarvan u de frequentie wilt tellen, u kunt deze naar behoefte wijzigen).


pijl blauwe rechterbel Tel de volgorde van voorkomen van duplicaten in Excel

Maar als u de volgorde van het voorkomen van de duplicaten wilt tellen, kunt u de volgende formule gebruiken.

Selecteer een lege cel naast de eerste gegevens in uw lijst en typ deze formule = AANTAL.ALS ($ A $ 2: $ A2, A2) (het bereik $ A $ 2: $ A2 geeft de lijst met gegevens aan, en A2 staat de cel waarvan u de bestelling wilt tellen, u kunt deze wijzigen zoals u nodig hebt) en druk vervolgens op invoerenen sleep de vulgreep om de kolom te vullen die u nodig hebt. Zie screenshot:


pijl blauwe rechterbel Tel en selecteer alle duplicaten in een kolom met Kutools voor Excel

Soms wilt u misschien alle duplicaten in een opgegeven kolom tellen en selecteren. U kunt het eenvoudig doen met Kutools voor Excel's Selecteer Duplicaten en unieke cellen utility.

1. Selecteer de kolom of lijst waarvan u alle duplicaten wilt tellen en klik op de Kutools > kiezen > Selecteer Duplicaten en unieke cellen.

2. Controleer in het dialoogvenster Select Duplicate & Unique Cells het selectievakje Duplicaten (behalve 1st één) optie of Alle duplicaten (inclusief 1st één) optie als je nodig hebt, en klik op de Ok knop.

En dan ziet u een dialoogvenster verschijnt dat toont hoeveel duplicaten zijn geselecteerd, en op hetzelfde moment dat duplicaten worden geselecteerd in de opgegeven kolom.

Let op: Als u alle duplicaten inclusief de eerste wilt tellen, moet u de Alle duplicaten (inclusief 1st één) optie in het dialoogvenster Select Duplicate & Unique Cells.

3. Klik op de OK knop.

Kutools for Excel - Bevat meer dan 300 handige tools voor Excel. Gratis proefversie 60-dag, geen creditcard vereist! Snap het nu


pijl blauwe rechterbel Aantal keer voorkomen van elk duplicaat in een kolom met Kutools voor Excel

Kutools voor Excel's Geavanceerd Combineer rijen hulpprogramma kan Excel-gebruikers helpen om batches van de exemplaren van elke items in een kolom te tellen (de gefruiteerde kolom in ons geval) en vervolgens de dubbele rijen op basis van deze kolom (de fruitkolom) eenvoudig te verwijderen, zoals hieronder:

1. Selecteer de tabel met de kolom waarin u elk duplicaat wilt tellen en klik op Kutools > Content > Geavanceerd Combineer rijen.

2. Selecteer in de geavanceerde combinatierijen de kolom waarin u elk duplicaat wilt tellen en klik op Hoofdsleutel, selecteer vervolgens de kolom waarin u de resultaten wilt tellen en klik Berekenen > Tellenen klik vervolgens op de OK knop. Zie screenshot:

En nu heeft het het voorkomen van elk duplicaat in de opgegeven kolom geteld. Zie screenshot:

Kutools for Excel - Bevat meer dan 300 handige tools voor Excel. Gratis proefversie 60-dag, geen creditcard vereist! Snap het nu


pijl blauwe rechterbelDemo: dubbele waarden in een kolom in Excel tellen door Kutools voor Excel

In deze video, Kutools en Kutools Plus tabbladen worden toegevoegd door Kutools for Excel. Klik indien nodig op voor 60-daagse gratis proef zonder beperking!

pijl blauwe rechterbelRelatieve artikelen:

Graaf samengevoegde cellen in Excel

Tel lege cellen of niet-lege cellen binnen een bereik in Excel


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen en gegevens bewaren; Inhoud gesplitste cellen; Combineer dubbele rijen en som / gemiddelde... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • Favoriete en snel formules invoegen, Bereiken, grafieken en afbeeldingen; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Werkt met Office 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in bedrijf. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
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 · 1 months ago
    Thanks so much, saved me hours!
  • To post as a guest, your comment is unpublished.
    patel akshay · 3 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 · 29 days 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 · 7 months ago
    Thank You very much. It's very useful.
  • To post as a guest, your comment is unpublished.
    BillBrewster · 8 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 · 6 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 · 1 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 · 4 years ago
    easy to use and helpful..:-p
  • To post as a guest, your comment is unpublished.
    munish · 4 years ago
    easy and helpful in large working
  • To post as a guest, your comment is unpublished.
    Zana · 4 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 · 4 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