Anzahl eindeutiger numerischer Werte basierend auf Kriterien in Excel

Im Excel-Arbeitsblatt kann es vorkommen, dass Sie die Anzahl eindeutiger numerischer Werte unter einer bestimmten Bedingung ermitteln müssen. Beispielsweise: Wie zähle ich die Anzahl der eindeutigen Mengenwerte (Qty) für das Produkt „T-Shirt“ aus dem im folgenden Screenshot gezeigten Bericht? In diesem Artikel stelle ich Ihnen einige Formeln vor, mit denen Sie diese Aufgabe in Excel effizient lösen können.

• Anzahl eindeutiger numerischer Werte basierend auf Kriterien in Excel 2019, 2016 und früher
• Anzahl eindeutiger numerischer Werte basierend auf Kriterien in Excel 365

Anzahl eindeutiger numerischer Werte basierend auf Kriterien in Excel 2019, 2016 und früher

In Excel 2019 und früheren Versionen können Sie die Funktionen SUMME, HÄUFIGKEIT und WENN kombinieren, um eine Formel zu erstellen, die die Anzahl eindeutiger Werte in einem Bereich basierend auf bestimmten Kriterien ermittelt. Die allgemeine Syntax lautet:

• criteria_range: Der Zellbereich, der mit den von Ihnen angegebenen Kriterien abgeglichen werden soll;
• criteria: Die Bedingung, auf deren Grundlage Sie Anzahl der eindeutigen Werte in einem Bereich möchten;
• Bereich: Der Zellbereich mit den eindeutigen Werten, die gezählt werden sollen.

Wenden Sie die folgende Formel in eine leere Zelle an und drücken Sie Strg + Umschalt + Enter, um das korrekte Ergebnis zu erhalten (siehe Screenshot):

Erläuterung der Formel:

=SUMME(--(HÄUFIGKEIT(WENN(A2:A12=E2;C2:C12);C2:C12)>0))

• WENN(A2:A12=E2;C2:C12): Diese WENN-Funktion gibt den Wert aus Spalte C zurück, wenn das Produkt in Spalte A „T-Shirt“ lautet. Das Ergebnis ist ein Array wie folgt: {FALSCH;300;500;FALSCH;400;FALSCH;300;FALSCH;FALSCH;FALSCH;350}.
• HÄUFIGKEIT(WENN(A2:A12=E2;C2:C12);C2:C12)= HÄUFIGKEIT({FALSCH;300;500;FALSCH;400;FALSCH;300;FALSCH;FALSCH;FALSCH;350};{200;300;500;350;400;450;300;550;200;260;350}): Die Funktion HÄUFIGKEIT zählt, wie oft jeder numerische Wert in der Array-Liste vorkommt, und liefert folgendes Ergebnis: {0;2;1;1;1;0;0;0;0;0;0;0}.
• --(HÄUFIGKEIT(WENN(A2:A12=E2;C2:C12);C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Prüft, ob jeder Wert im Array größer als 0 ist, und liefert folgendes Ergebnis: {FALSCH;WAHR;WAHR;WAHR;WAHR;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH}. Anschließend wandelt das doppelte Minuszeichen die WAHR- und FALSCH-Werte in 1 und 0 um und gibt ein Array wie folgt zurück: {0;1;1;1;1;0;0;0;0;0;0;0}.
• SUMME(--(HÄUFIGKEIT(WENN(A2:A12=E2;C2:C12);C2:C12)>0))=SUMME({0;1;1;1;1;0;0;0;0;0;0;0}): Abschließend werden diese Werte mit der SUMME-Funktion addiert, um die Gesamtanzahl zu erhalten: 4.

Tipps:

Wenn Sie die eindeutigen Werte basierend auf mehr als einer Bedingung zählen möchten, fügen Sie einfach weitere Kriterien mit dem *-Zeichen zur Formel hinzu:

Anzahl eindeutiger numerischer Werte basierend auf Kriterien in Excel 365

In Excel 365 ermöglicht die Kombination der Funktionen ZEILEN, EINDEUTIG und FILTER das Zählen eindeutiger numerischer Werte basierend auf festgelegten Kriterien. Die allgemeine Syntax lautet:

• range: Der Bereich der Zellen mit den eindeutigen Werten, die gezählt werden sollen.
• criteria_range: Der Zellbereich, der mit den von Ihnen angegebenen Kriterien abgeglichen werden soll;
• criteria: Die Bedingung, auf deren Grundlage Sie Anzahl der eindeutigen Werte in einem Bereich möchten;

Kopieren oder geben Sie die folgende Formel in eine Zelle ein und drücken Sie die Enter-Taste, um das Ergebnis zu erhalten (siehe Screenshot):

Erläuterung der Formel:

=ZEILEN(EINDEUTIG(FILTER(C2:C12;A2:A12=E2)))

• A2:A12=E2: Dieser Ausdruck prüft, ob der Wert in Zelle E2 im Bereich A2:A12 enthalten ist, und liefert folgendes Ergebnis: {FALSCH;WAHR;WAHR;FALSCH;WAHR;FALSCH;WAHR;FALSCH;FALSCH;FALSCH;WAHR}.
• FILTER(C2:C12;A2:A12=E2): Die Funktion HÄUFIGKEIT zählt jedes der numerischen Werte in der Array-Liste und liefert folgendes Ergebnis zurück: {0;2;1;1;350;0;0;0;0;0;0;0}.
• EINDEUTIG(FILTER(C2:C12;A2:A12=E2))=EINDEUTIG({300;500;400;300;350}): Hier extrahiert die Funktion EINDEUTIG alle eindeutigen Werte aus dem Array – das Ergebnis lautet: {300;500;400;350}.
• ZEILEN(EINDEUTIG(FILTER(C2:C12;A2:A12=E2)))=ZEILEN({300;500;400;350}): Die Funktion ZEILEN gibt die Anzahl der Zeilen eines Zellbereichs oder Arrays zurück – das Ergebnis lautet daher: 4.

Tipps:

1. Wenn der übereinstimmende Wert im Datenbereich nicht vorhanden ist, erhalten Sie einen Fehlerwert. Ersetzen Sie diesen durch 0 mithilfe der folgenden Formel:

2. Um die eindeutigen Werte basierend auf mehreren Bedingungen zu zählen, fügen Sie einfach weitere Kriterien mit dem *-Zeichen zur Formel hinzu, wie folgt:

Verwendete verwandte Funktion:

Weitere Artikel: