Dubbele waarden vinden en tellen in Excel

De snelste manier om dubbelen te vinden is een hulpkolom. Zet naast je lijst, bijvoorbeeld in C2:

=AANTAL.ALS($A$2:$A$100;A2)>1

Trek de formule door naar beneden. AANTAL.ALS telt hoe vaak de waarde uit de eigen rij in de hele kolom voorkomt. Is dat meer dan 1, dan staat er WAAR. Bij een unieke waarde staat er ONWAAR.

De dollartekens zijn belangrijk. Het bereik $A$2:$A$100 moet bij het doortrekken op zijn plek blijven, terwijl A2 wel meeschuift naar A3, A4 en zo verder.

Liever een woord dan WAAR of ONWAAR? Zet er ALS omheen: =ALS(AANTAL.ALS($A$2:$A$100;A2)>1;"Dubbel";""). Zet daarna een filter op de hulpkolom, dan zie je alle dubbele rijen bij elkaar.

Laat je >1 weg, dan zie je per rij hoe vaak de waarde voorkomt:

=AANTAL.ALS($A$2:$A$100;A2)

Een 1 is uniek, een 3 betekent dat de waarde drie keer in de lijst staat.

Wil je liever een overzicht met elke waarde één keer, maak dan eerst een lijst zonder dubbelen. In Excel 365 typ je in D2 =UNIEK(A2:A100). In E2 zet je =AANTAL.ALS($A$2:$A$100;D2) en die trek je door tot onder aan de lijst. Zo krijg je een telling per waarde, zoals Koffie 4 en Thee 2.

In Excel 365 kan het ook in één keer met =AANTAL.ALS(A2:A100;D2#). Het hekje verwijst naar de hele lijst die UNIEK in D2 maakt.

Een draaitabel met de kolom bij Rijen en bij Waarden geeft hetzelfde overzicht, met Aantal van.

Op die vraag zijn drie antwoorden mogelijk. Kies de formule die past bij wat je wilt weten:

• Hoeveel rijen hebben een dubbele waarde? =AANTALARG(A2:A100)-AANTALARG(UNIEK(A2:A100;ONWAAR;WAAR)). Heb je al een hulpkolom met de aantallen per rij in kolom C, dan kan het ook met =AANTAL.ALS(C2:C100;">1").
• Hoeveel rijen zijn overbodig? Dat zijn de rijen die Duplicaten verwijderen zou weghalen: =AANTALARG(A2:A100)-AANTALARG(UNIEK(A2:A100)).
• Hoeveel verschillende waarden komen dubbel voor? =AANTALARG(UNIEK(A2:A100))-AANTALARG(UNIEK(A2:A100;ONWAAR;WAAR)).

Komt Koffie vier keer voor en Thee twee keer, dan zijn er 6 rijen met een dubbele waarde, 4 overbodige rijen en 2 waarden die dubbel voorkomen.

Gebruik een bereik zonder lege cellen. In Excel 2019 en ouder, zonder UNIEK, gebruik je voor de eerste vraag =SOMPRODUCT((AANTAL.ALS(A2:A100;A2:A100)>1)*1).

Wil je de eerste keer laten staan en alleen de herhalingen markeren, laat het bereik dan meegroeien:

=AANTAL.ALS($A$2:A2;A2)>1

Alleen het begin van het bereik staat vast. In rij 2 kijkt de formule alleen naar A2, in rij 10 naar A2 tot en met A10. De eerste keer dat een waarde voorkomt, geeft dus ONWAAR. Pas de tweede, derde en volgende keer geven WAAR.

Dat is precies wat Duplicaten verwijderen ook doet: de bovenste blijft staan, de rest gaat weg. Met deze hulpkolom zie je vooraf welke rijen dat zijn. Filter op WAAR en controleer ze, voordat je iets weghaalt.

Soms is een rij pas dubbel als meerdere kolommen samen gelijk zijn. Dezelfde naam op een andere datum is bijvoorbeeld geen dubbele inschrijving. Gebruik dan AANTALLEN.ALS, met een paar van bereik en waarde per kolom:

=AANTALLEN.ALS($A$2:$A$100;A2;$B$2:$B$100;B2)>1

Deze formule geeft alleen WAAR als de naam en de datum samen vaker voorkomen. Je kunt zo veel kolommen toevoegen als je nodig hebt.

Wil je dubbele waarden alleen zien, zonder hulpkolom, kleur ze dan. Selecteer de kolom en kies op het tabblad Start voor Voorwaardelijke opmaak, Markeringsregels voor cellen en dan Dubbele waarden.

Wil je ze daarna echt weghalen, gebruik dan Duplicaten verwijderen op het tabblad Gegevens. Hoe dat stap voor stap werkt, lees je in de gids Dubbele waarden verwijderen.