ZÄHLENWENNS mit ODER-Logik für mehrere Kriterien in Excel

Normalerweise können Sie die Funktion ZÄHLENWENNS nutzen, um Zellen in Excel basierend auf einer oder mehreren Bedingungen mit UND-Logik zu zählen. Sind Sie schon einmal in eine Situation geraten, in der Sie mehrere Werte aus einer einzigen Spalte oder einem bestimmten Zellbereich zählen mussten? Das bedeutet, dass Sie mit mehreren Bedingungen und ODER-Logik arbeiten müssen. In diesem Fall kombinieren Sie am besten die Funktionen SUMME und ZÄHLENWENNS – oder Sie verwenden direkt SUMMENPRODUKT.

• Zellen mit ODER-Bedingungen in Excel zählen
  • Zählen von Zellen mit ODER-Bedingungen mithilfe der Funktionen SUMME und ZÄHLENWENNS
  • Zählen von Zellen mit ODER-Bedingungen mithilfe der Funktion SUMMENPRODUKT
• Zellen mit mehreren Gruppen von ODER-Bedingungen in Excel zählen
  • Zählen von Zellen mit zwei Gruppen von ODER-Bedingungen mithilfe der Funktionen SUMME und ZÄHLENWENNS
  • Zählen von Zellen mit mehreren Gruppen von ODER-Bedingungen mithilfe der Funktion SUMMENPRODUKT

Zellen mit ODER-Bedingungen in Excel zählen

Angenommen, Sie haben einen Datenbereich wie im folgenden Screenshot gezeigt und möchten die Anzahl der Produkte ermitteln, die „Bleistift“ oder „Lineal“ sind. Im Folgenden stelle ich Ihnen zwei Formeln vor, mit denen Sie diese Aufgabe in Excel lösen können.

Zählen von Zellen mit ODER-Bedingungen mithilfe der Funktionen SUMME und ZÄHLENWENNS

In Excel können Sie zur Zählung mit mehreren ODER-Bedingungen die Funktionen SUMME und ZÄHLENWENNS in Kombination mit einer Array-Konstante nutzen. Die generische Syntax lautet:

• range: Der Datenbereich enthält die Kriterien, aus denen Sie Zellen zählen;
• Kriterium1, Kriterium2, Kriterium3 …: Die Bedingungen, anhand derer Sie Zellen zählen möchten.

Um die Anzahl der Produkte zu zählen, die „Bleistift“ oder „Lineal“ sind, kopieren oder geben Sie die folgende Formel in eine leere Zelle ein und drücken Sie anschließend die Eingabetaste, um das Ergebnis zu erhalten:

Erläuterung der Formel:

=SUM(COUNTIFS(B2:B13,{"Pencil",„Ruler"}))

• {"Bleistift",„Lineal"}: Zunächst sollten Sie alle Bedingungen in eine Array-Konstante wie diese einfügen: {"Bleistift",„Lineal"}. Trennen Sie die Elemente dabei durch Kommas.
• ZÄHLENWENNS(B2:B13,{"Bleistift",„Lineal"}): Diese ZÄHLENWENNS-Funktion ermittelt die Anzahl der Vorkommen von „Bleistift“ und „Lineal“ separat – das Ergebnis lautet: {2;3}.
• SUMME(ZÄHLENWENNS(B2:B13,{"Bleistift",„Lineal"}))=SUMME({2,3}): Schließlich summiert die SUMME-Funktion alle Elemente des Arrays und liefert das Ergebnis: 5.

Tipp: Sie können auch Zellbezüge als Kriterien verwenden. Wenden Sie dazu die folgende Array-Formel an und drücken Sie anschließend gleichzeitig Strg + Umschalt + Eingabe, um das korrekte Ergebnis zu erhalten:

Zählen von Zellen mit ODER-Bedingungen mithilfe der Funktion SUMMENPRODUKT

Hier ist eine weitere Formel, die mithilfe der Funktion SUMMENPRODUKT erstellt wurde und ebenfalls beim Zählen von Zellen mit ODER-Logik unterstützt. Die generische Syntax lautet:

• range: Der Datenbereich enthält die Kriterien, aus denen Sie Zellen zählen;
• criterion1, criterion2, criterion3…: Die Bedingungen, auf deren Grundlage Sie Zellen zählen möchten.

Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein und drücken Sie die Eingabetaste, um das Ergebnis zurückzugeben:

Erläuterung der Formel:

=SUMPRODUCT(1*(B2:B13={"Pencil",„Ruler"}))

• B2:B13={"Bleistift",„Lineal"}: Dieser Ausdruck vergleicht jedes der Kriterien „Bleistift“ und „Lineal“ mit dem Bereich B2:B13. Ist ein Kriterium erfüllt, gibt die Formel WAHR zurück – andernfalls FALSCH. Das Ergebnis sieht wie folgt aus: {WAHR,FALSCH;FALSCH,FALSCH;FALSCH,FALSCH;FALSCH,WAHR;FALSCH,FALSCH;WAHR,FALSCH;FALSCH,FALSCH;FALSCH,WAHR;FALSCH,FALSCH;FALSCH,FALSCH;FALSCH,WAHR;FALSCH,FALSCH}.
• 1*(B2:B13={"Bleistift",„Lineal"}): Durch die Multiplikation werden die logischen Werte – WAHR und FALSCH – in 1 und 0 umgewandelt, sodass folgendes Ergebnis entsteht: {1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}.
• SUMMENPRODUKT(1*(B2:B13={"Bleistift",„Lineal"}))= SUMMENPRODUKT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Am Ende addiert die SUMMENPRODUKT-Funktion alle Zahlen im Array und liefert das Ergebnis: 5.

Zellen mit mehreren Gruppen von ODER-Bedingungen in Excel zählen

Manchmal müssen Sie Zellen zählen, die zwei oder mehr Gruppen von ODER-Bedingungen erfüllen. In diesem Fall können Sie entweder SUMME und ZÄHLENWENNS mit einer Array-Konstante oder SUMMENPRODUKT in Kombination mit den Funktionen ISTZAHL und VERGLEICH verwenden.

Zählen von Zellen mit zwei Gruppen von ODER-Bedingungen mithilfe der Funktionen SUMME und ZÄHLENWENNS

Für genau zwei Gruppen von ODER-Kriterien fügen Sie der ZÄHLENWENNS-Formel einfach eine weitere Array-Konstante hinzu.

Angenommen, ich habe den Datenbereich wie im folgenden Screenshot gezeigt und möchte nun die Personen zählen, die „Bleistift“ oder „Lineal“ bestellt haben und deren Bestellbetrag genau 200 beträgt.

Geben oder kopieren Sie die folgende Formel in eine Zelle ein und drücken Sie anschließend die Eingabetaste, um das Ergebnis zu erhalten (siehe Screenshot):

: Verwenden Sie in der Formel für die zweite Array-Konstante ein Semikolon, um ein vertikales Array zu erstellen.

Zählen von Zellen mit mehreren Gruppen von ODER-Bedingungen mithilfe der Funktion SUMMENPRODUKT

Die obige Formel funktioniert nur für zwei Gruppen von ODER-Kriterien. Müssen Sie weitere Kriterien berücksichtigen, hilft Ihnen eine komplexe SUMMENPRODUKT-Formel in Kombination mit den Funktionen ISTZAHL und VERGLEICH.

Nehmen Sie beispielsweise folgende Daten: Um die Personen zu zählen, die „Bleistift“ oder „Lineal“ bestellt haben, deren Bestellstatus „Geliefert“ oder „Unterwegs“ ist und deren Auftrag von „Bob“ oder „Eko“ unterschrieben wurde, benötigen Sie eine komplexe Formel.

Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein und drücken Sie anschließend die Eingabetaste, um die Berechnung durchzuführen (siehe Screenshot):

Erläuterung der Formel:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil",„Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered",„In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob",„Eko"},0)))

ISNUMBER(MATCH(B2:B13;{"Pencil";„Ruler"};0)):

• VERGLEICH(B2:B13,{"Bleistift",„Lineal"},0): Diese VERGLEICH-Funktion vergleicht jede Zelle im Bereich B2:B13 mit der entsprechenden Array-Konstante. Sobald eine Übereinstimmung vorliegt, gibt sie die relative Position des Werts innerhalb des Arrays zurück – andernfalls erscheint ein Fehlerwert. Das Ergebnis ist folgende Array-Liste: {1;#NV;#NV;2;#NV;1;#NV;2;1;#NV;2;#NV}.
• ISTZAHL(VERGLEICH(B2:B13,{"Bleistift",„Lineal"},0))= ISTZAHL({1;#NV;#NV;2;#NV;1;#NV;2;1;#NV;2;#NV}): Die ISTZAHL-Funktion wandelt Zahlen in WAHR-Werte und Fehlerwerte in FALSCH-Werte um – und zwar wie folgt: {WAHR;FALSCH;FALSCH;WAHR;FALSCH;WAHR;FALSCH;WAHR;WAHR;FALSCH;WAHR;FALSCH}.

Die obige Logik kann auch auf den zweiten und dritten ISNUMBER-Ausdruck angewendet werden.

SUMPRODUCT(ISNUMBER(MATCH(B2:B13;{"Pencil";„Ruler"};0))*ISNUMBER(MATCH(C2:C13;{"Delivered";„In transit"};0))*ISNUMBER(MATCH(D2:D13;{"Bob";„Eko"};0))):

• Anschließend werden diese drei Arrays innerhalb von SUMMENPRODUKT miteinander multipliziert – dabei wandelt Excel WAHR- und FALSCH-Werte automatisch im Zuge der mathematischen Operation in 1 bzw. 0 um, wie hier veranschaulicht: SUMMENPRODUKT({1;0;0;1;0;1;0;1;1;0;1;0}*{1;1;0;0;1;1;0;1;1;0;1;1}*{1;0;0;0;0;0;0;1;0;0;1;0}) = SUMMENPRODUKT({1;0;0;0;0;0;0;1;0;0;1;0}).
• Schließlich summiert die SUMMENPRODUKT-Funktion alle Zahlen im Array und liefert das Ergebnis: 3.

Verwendete relative Funktion:

Weitere Artikel: