Generische Formel
SUMPRODUCT(--(A:A=A1))
Zusammenfassung
Vorwort
Dies ist eine ärgerlich lange Einführung, aber der Kontext ist wichtig, sorry!
Wenn Sie versuchen, mit COUNTIF sehr lange Zahlen (mehr als 16 Stellen) in einem Bereich zu zählen, werden möglicherweise falsche Ergebnisse angezeigt, da bestimmte Funktionen nicht mit langen Zahlen umgehen, selbst wenn diese Zahlen als Text gespeichert sind. Betrachten Sie den Bildschirm unten. Alle Zählungen in Spalte D sind falsch - obwohl jede Zahl in Spalte B eindeutig ist, deutet die von COUNTIF zurückgegebene Zählung darauf hin, dass diese Zahlen Duplikate sind.
=COUNTIF(data,B5)
Dieses Problem hängt damit zusammen, wie Excel mit Zahlen umgeht. Excel kann nur 15 signifikante Ziffern verarbeiten. Wenn Sie in Excel eine Zahl mit mehr als 15 Ziffern eingeben, werden die nachfolgenden Ziffern stillschweigend in Null konvertiert. Das oben erwähnte Zählproblem ergibt sich aus dieser Grenze.
Normalerweise können Sie diese Begrenzung vermeiden, indem Sie lange Zahlen als Text eingeben, indem Sie die Zahl entweder mit einem einfachen Anführungszeichen ('999999999999999999) beginnen oder die Zelle (n) vor der Eingabe als Text formatieren. Solange Sie keine mathematischen Operationen an einer Nummer ausführen müssen, ist dies eine gute Lösung. Sie können damit extra lange Nummern für Dinge wie Kreditkartennummern und Seriennummern eingeben, ohne Nummern zu verlieren.
Wenn Sie jedoch versuchen, mit COUNTIF eine Zahl mit mehr als 15 Ziffern zu zählen (auch wenn diese als Text gespeichert ist), werden möglicherweise unzuverlässige Ergebnisse angezeigt. Dies liegt daran, dass COUNTIF den langen Wert zu einem bestimmten Zeitpunkt während der Verarbeitung intern wieder in eine Zahl umwandelt, wodurch die oben beschriebene 15-stellige Grenze ausgelöst wird. Ohne alle vorhandenen Ziffern können einige Zahlen beim Zählen mit COUNTIF wie Duplikate gezählt werden.
Lösung
Eine Lösung besteht darin, die COUNTIF-Formel durch eine Formel zu ersetzen, die SUM oder SUMPRODUCT verwendet. Im gezeigten Beispiel sieht die Formel in E5 folgendermaßen aus:
=SUMPRODUCT(--(data=B5))
Die Formel verwendet den benannten Bereich "Daten" (B5: B9) und generiert mit SUMPRODUCT die richtige Anzahl für jede Zahl.
Erläuterung
Zunächst vergleicht der Ausdruck in SUMPRODUCT alle Werte im benannten Bereich "data" mit dem Wert aus Spalte B in der aktuellen Zeile. Dies führt zu einem Array von TRUE / FALSE-Ergebnissen.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Als nächstes zwingt das doppelte Negativ die TRUE / FALSE-Werte zu 1/0 Werten.
=SUMPRODUCT((1;0;0;0;0))
Schließlich summiert SUMPRODUCT einfach die Elemente im Array und gibt das Ergebnis zurück.
Array-Formelvariante
Sie können auch die SUMME-Funktion anstelle von SUMPRODUCT verwenden. Dies ist jedoch eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden:
(=SUM(--(B:B=B5)))
Andere Funktionen mit diesem Problem
Ich habe dies selbst nicht überprüft, aber es scheint, dass mehrere Funktionen das gleiche Problem haben, einschließlich SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF und AVERAGEIFS.
