Duplikate zählen in Excel – Eindeutige und mehrfache Werte ermitteln

Duplikate zählen ist eine häufige und wichtige Aufgabe – und es gibt mehrere Wege dorthin!

Das Problem: Du hast eine Liste mit Kundennummern, E-Mails oder Produkten und möchtest wissen:

• Wie viele EINDEUTIGE Werte gibt es? (Nur 1x zählen)
• Welche Werte sind DUPLIKATE? (Kommen 2x oder mehr vor)
• Wie OFT kommt jeder Wert vor? (Zähle die Vorkommen)
• Nur die MEHRFACHEN Werte zählen (nicht die eindeutigen)

Die Werkzeuge: ZÄHLENWENN, SUMMPRODUCT, UNIQUE, FILTER und mehr!

In diesem Tutorial lernst du: Wie du Duplikate effizient zählst, findest und analysierst!

---

1. Grundszenario – Deine Liste

Beispiel-Kundenliste:

A (Kundennummer)	B (Anzahl Vorkommen)	C (Eindeutig/Duplikat?)
K001	???	???
K002	???	???
K001	???	???
K003	???	???
K002	???	???
K001	???	???

Fragen:

• Wie viele eindeutige Nummern? (K001, K002, K003 = 3)
• Welche sind Duplikate? (K001 kommt 3x vor, K002 kommt 2x vor)
• Wie oft kommt K001 vor? (3x)

---

2. Methode 1: ZÄHLENWENN – Häufigkeit zählen

Die häufigste Methode – zähle wie oft ein Wert vorkommt.

=ZÄHLENWENN(Bereich, Kriterium)

Praktisches Beispiel:

In Spalte B (Häufigkeit):

=ZÄHLENWENN($A$2:$A$7, A2)

Erklärung:

• $A$2:$A$7 = Der Gesamtbereich (absolute Referenz!)
• A2 = Der aktuelle Wert (relative Referenz)

Ergebnis:

• K001 → 3 (kommt 3x vor)
• K002 → 2 (kommt 2x vor)
• K003 → 1 (kommt 1x vor – eindeutig!)

✅ Tipp: Nutze absolute Referenzen für den Gesamtbereich ($A$2:$A$7) damit er nicht verschoben wird!

---

3. Methode 2: Eindeutige Werte zählen (UNIQUE)

Nur in Excel 365 verfügbar – aber sehr elegante Lösung!

UNIQUE-Funktion erklärt:

=UNIQUE(Bereich, [Duplikate_ignorieren], [Spalte_spezifisch])

Zähle alle eindeutigen Werte:

Formel:

=COUNTA(UNIQUE(A2:A7))

Erklärung:

• UNIQUE(A2:A7) = Alle eindeutigen Werte auflisten
• COUNTA(...) = Zähle sie

Ergebnis: 3 (K001, K002, K003)

⚠️ ACHTUNG: UNIQUE ist nur in Excel 365 verfügbar. Für ältere Versionen nutze andere Methoden!

---

4. Methode 3: SUMPRODUCT – Die universelle Lösung

Funktioniert in ALLEN Excel-Versionen – sehr flexibel!

Zähle eindeutige Werte:

Formel:

=SUMPRODUCT(1/ZÄHLENWENN(A2:A7,A2:A7))

Ergebnis: 3 (eindeutige Werte)

Erklärung (mathematisch):

• ZÄHLENWENN(A2:A7,A2:A7) = Wie oft kommt jeder Wert vor?
• 1/ = Teile 1 durch die Häufigkeit (Duplikate werden kleiner)
• SUMPRODUCT = Addiere alle Quotienten (Duplikate ergeben zusammen 1)

Zähle nur die Duplikate (mehrfache Werte):

Formel:

=SUMPRODUCT((ZÄHLENWENN(A2:A7,A2:A7)>1)*1)

Ergebnis: 5 (K001 3x + K002 2x = 5 Zeilen mit Duplikaten)

Oder: Zähle nur die DUPLIKAT-WERTE (nicht die Zeilen):**

=SUMPRODUCT(--(ZÄHLENWENN(A2:A7,A2:A7)>1),--(ZÄHLENWENN(A$2:A2,A2:A7)=1))

---

5. Methode 4: Duplikate mit WENN + ZÄHLENWENN markieren

Markiere jede Zeile als "Eindeutig" oder "Duplikat".

In Spalte C (Eindeutig/Duplikat):

=WENN(ZÄHLENWENN($A$2:$A$7,A2)=1,"Eindeutig","Duplikat")

Ergebnis:

• K001 → "Duplikat" (kommt 3x vor)
• K002 → "Duplikat" (kommt 2x vor)
• K003 → "Eindeutig" (kommt 1x vor)

---

6. Methode 5: Filter nur Duplikate (Excel 365)

Zeige NUR die Duplikate an – sehr nützlich zur Überprüfung!

Formel:

=FILTER(A2:A7, ZÄHLENWENN(A$2:A$7,A2:A7)>1)

Ergebnis: Nur K001, K001, K002, K001, K002 (alle Duplikate)

---

7. Praktisches Szenario – Kundenanalyse

Deine Aufgabe:

• Wie viele verschiedene Kunden haben bestellt?
• Welche Kunden haben mehrfach bestellt? (Loyalität?)
• Wer sind die Mehrfach-Käufer?

Deine Kundenliste:

Kundennummer	Häufigkeit	Status
K001	=ZÄHLENWENN($A$2:$A$7,A2)	=WENN(B2>1,"Stammkunde","Neukunde")

Analysen:

Eindeutige Kunden: =SUMPRODUCT(1/ZÄHLENWENN(A2:A7,A2:A7)) Stammkunden (>1x): =SUMPRODUCT((ZÄHLENWENN(A2:A7,A2:A7)>1)/ZÄHLENWENN(A2:A7,A2:A7&"")) Gesamt-Käufe: =COUNTA(A2:A7)

---

8. Häufige Fehler & Lösungen

❓ F: SUMPRODUCT gibt #DIV/0! Fehler!

A: Das passiert wenn leere Zellen im Bereich sind!

Lösung:

=SUMPRODUCT((A2:A7<>"")/ZÄHLENWENN(A2:A7,A2:A7&""))

Das schließt leere Zellen aus!

❓ F: Ich brauche PROZENTUALE Duplikate!

A: Teile die Duplikat-Anzahl durch die Gesamtzahl:

=SUMPRODUCT((ZÄHLENWENN(A2:A7,A2:A7)>1)*1)/COUNTA(A2:A7)

Beispiel: 5 Duplikate von 6 Zeilen = 83%

❓ F: Ich brauche nur die ERSTEN Duplikate!

A: Nutze ZÄHLENWENN mit wachsendem Bereich:

=WENN(ZÄHLENWENN($A$2:$A2,A2)>1,"Duplikat","Eindeutig")

Der erste K001 ist "Eindeutig", die 2. & 3. sind "Duplikat"!

❓ F: Wie zähle ich Duplikate über mehrere Spalten?

A: Kombiniere die Spalten zu einem Kriterium:

=ZÄHLENWENN($A$2:$A$7&$B$2:$B$7, A2&B2)

Das berücksichtigt BEIDE Spalten!

---

9. Vergleich aller Methoden

Methode	Excel-Version	Was zählt	Syntax	Best für
ZÄHLENWENN	✅ Alle	Häufigkeit einzelner Werte	=ZÄHLENWENN($A$2:$A$7,A2)	Pro Wert zählen
UNIQUE + COUNTA	❌ Nur 365	Eindeutige Werte	=COUNTA(UNIQUE(A2:A7))	Schnelle Übersicht (365)
SUMPRODUCT	✅ Alle	Eindeutige Werte	=SUMPRODUCT(1/ZÄHLENWENN(A2:A7,A2:A7))	Universelle Lösung
WENN + ZÄHLENWENN	✅ Alle	Eindeutig/Duplikat markieren	=WENN(ZÄHLENWENN($A$2:$A$7,A2)=1,"E","D")	Visuelle Markierung
FILTER	❌ Nur 365	Nur Duplikate anzeigen	=FILTER(A2:A7, ZÄHLENWENN(A$2:A$7,A2:A7)>1)	Duplikate filtern (365)

---

10. Best Practices & Tipps

✅ Tipp 1: Immer absolute Referenzen für Gesamtbereich

=ZÄHLENWENN($A$2:$A$7, A2) ✅ Richtig =ZÄHLENWENN(A2:A7, A2) ❌ Falsch

✅ Tipp 2: Kombiniere mit WENN für Kategorisierung

Eindeutig vs. Duplikat sichtbar machen!

✅ Tipp 3: Nutze SUMPRODUCT für Universalität

SUMPRODUCT funktioniert in ALLEN Excel-Versionen – verwende es wenn du unsicher bist!

✅ Tipp 4: Bedingte Formatierung für Duplikate

Nutze bedingte Formatierung um Duplikate farblich hervorzuheben!

✅ Tipp 5: Mehrere Spalten kombinieren

Wenn Duplikate über mehrere Spalten definiert sind (z.B. Name + Vorname), kombiniere sie mit &

---

11. Fallstudie: E-Mail-Liste bereinigen

Szenario: Du hast eine E-Mail-Liste und möchtest wissen:

• Wie viele verschiedene E-Mails?
• Welche E-Mails sind Duplikate?
• Wieviel Duplikate können gelöscht werden?

Analysen:

Gesamte E-Mails: =COUNTA(A2:A7) Eindeutige E-Mails: =SUMPRODUCT(1/ZÄHLENWENN(A2:A7,A2:A7)) Duplikat-Zeilen: =COUNTA(A2:A7)-SUMPRODUCT(1/ZÄHLENWENN(A2:A7,A2:A7)) Prozentuale Duplikate: =(COUNTA(A2:A7)-SUMPRODUCT(1/ZÄHLENWENN(A2:A7,A2:A7)))/COUNTA(A2:A7)

Beispiel-Ergebnis:

• Gesamte E-Mails: 100
• Eindeutige E-Mails: 85
• Duplikat-Zeilen: 15 (können gelöscht werden)
• Prozentuale Duplikate: 15%

---

12. Deine nächsten Schritte

1. Test-Daten erstellen: Mit bekannten Duplikaten
2. ZÄHLENWENN testen: Pro Wert zählen
3. Eindeutige zählen: SUMPRODUCT probieren
4. Duplikate markieren: Mit WENN
5. In echten Daten: E-Mails, Kundennummern, etc.
6. Analyse erstellen: Mit allen wichtigen Metriken

---

Checkliste für dein Projekt

• Ich kenne ZÄHLENWENN & kann Häufigkeiten zählen
• Ich weiß wie ich absolute Referenzen nutze
• Ich kann eindeutige Werte mit SUMPRODUCT zählen
• Ich kann WENN + ZÄHLENWENN kombinieren
• Ich kenne den Unterschied zwischen Zeilen & Werten
• Ich kann Duplikate markieren & visualisieren
• Ich kenne UNIQUE & FILTER (Excel 365)
• Ich kann Duplikate über mehrere Spalten finden
• Ich kann Fehler (#DIV/0!) behandeln
• Ich erstelle aussagekräftige Duplikat-Analysen

---

Mit diesen Techniken beherrschst du Duplikat-Analyse wie ein Profi! 🔍