Countifs mit ODER-Logik für mehrere Kriterien in Excel
Normalerweise können Sie die Funktion ZÄHLENWENN verwenden, um Zellen basierend auf einer oder mehreren Bedingungen mit UND-Logik in Excel zu zählen. Haben Sie jemals eine Situation erlebt, in der Sie mehr als einen Wert aus einer einzelnen Spalte oder einem Zellbereich zählen mussten? Das bedeutet, mit mehreren Bedingungen und ODER-Logik zu zählen. In diesem Fall können Sie die Funktionen SUMME und ZÄHLENWENN zusammen anwenden oder die Funktion SUMMENPRODUKT verwenden.
- Zellen mit ODER-Bedingungen in Excel zählen
- Zählen Sie Zellen mit mehreren Sätzen von ODER-Bedingungen in Excel
Zellen mit ODER-Bedingungen in Excel zählen
Zum Beispiel habe ich eine Reihe von Daten, wie unten im Screenshot gezeigt. Jetzt möchte ich die Nummer des Produkts zählen, das "Bleistift" oder "Lineal" ist. Hier werde ich zwei Formeln zur Lösung dieser Aufgabe in Excel diskutieren.
Zählen Sie Zellen mit ODER-Bedingungen, indem Sie die Funktionen SUM und COUNTIFS verwenden
Um in Excel mit mehreren ODER-Bedingungen zu zählen, können Sie die Funktionen SUM und ZÄHLENWENN mit einer Array-Konstante verwenden. 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.
Um die Anzahl der Produkte zu zählen, die "Bleistift" oder "Lineal" sind, kopieren Sie bitte die folgende Formel oder geben Sie sie in eine leere Zelle ein und drücken Sie dann Enter Schlüssel, um das Ergebnis zu erhalten:
Erklärung der Formel:
=SUMME(ZÄHLENWENN(B2:B13,{"Bleistift","Lineal"}))
- {"Bleistift","Lineal"}: Zuerst sollten Sie alle Bedingungen in eine Array-Konstante wie diese packen: {"Pencil","Ruler"}, trennen Sie die Elemente durch Kommas.
- ZÄHLEN(B2:B13,{"Pencil","Lineal"}): Diese ZÄHLENWENN-Funktion erhält individuelle Zählungen für „Bleistift“ und „Lineal“, und Sie erhalten das Ergebnis wie folgt: {2,3}.
- SUMME(ZÄHLENWENN(B2:B13,{"Bleistift","Lineal"}))=SUMME({2,3}): Schließlich summiert diese SUM-Funktion alle Elemente im Array und gibt das Ergebnis zurück: 5.
Tips: Sie können auch Zellbezüge für Kriterien verwenden, wenden Sie bitte die folgende Matrixformel an und drücken Sie dann Strg + Umschalt + Enter Tasten zusammen, um das richtige Ergebnis zu erhalten:
Zählen Sie Zellen mit ODER-Bedingungen, indem Sie die SUMPRODUCT-Funktion verwenden
Hier ist eine weitere Formel, die von der SUMPRODUCT-Funktion erstellt wird, die auch beim Zählen von Zellen mit ODER-Logik helfen kann. 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.
Bitte kopieren Sie die folgende Formel oder geben Sie sie in eine leere Zelle ein und drücken Sie Enter Taste, um das Ergebnis zurückzugeben:
Erklärung der Formel:
=SUMMENPRODUKT(1*(B2:B13={"Bleistift","Lineal"}))
- B2:B13={"Bleistift","Lineal"}: Dieser Ausdruck vergleicht jedes Kriterium „Bleistift“, „Lineal“ mit der Bereichszelle B2:B13. Wenn das Kriterium erfüllt ist, wird TRUE zurückgegeben, andernfalls wird FALSE angezeigt, Sie erhalten das Ergebnis wie folgt: {TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE ;FALSCH,FALSCH;FALSCH,WAHR;FALSCH,FALSCH;FALSCH,FALSCH;FALSCH,WAHR;FALSCH,FALSCH}.
- 1*(B2:B13={"Bleistift","Lineal"}): Multiplikation wandelt die logischen Werte – TRUE und FALSE in 1 und 0 um, sodass das Ergebnis wie folgt lautet: {1,0;0,0;0,0;0,1;0,0;1,0;0,0 ,0,1;0,0;0,0;0,1;0,0;XNUMX}.
- SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Schließlich addiert die SUMPRODUCT-Funktion alle Zahlen im Array, um das Ergebnis zu erhalten: 5.
Zählen Sie Zellen mit mehreren Sätzen von ODER-Bedingungen in Excel
Manchmal müssen Sie die Zellen möglicherweise mit zwei oder mehr Sätzen von ODER-Bedingungen zählen. In diesem Fall können Sie entweder SUM und ZÄHLENWENN mit einer Arraykonstanten oder SUMPRODUCT mit ISNUMBER MATCH-Funktionen verwenden.
Zählen Sie Zellen mit zwei Sätzen von ODER-Bedingungen, indem Sie die Funktionen SUMME und ZÄHLENWENN verwenden
Um nur zwei Sätze von ODER-Kriterien zu behandeln, müssen Sie der Formel ZÄHLENWENN nur eine weitere Array-Konstante hinzufügen.
Zum Beispiel habe ich den Datenbereich wie unten im Screenshot gezeigt, jetzt möchte ich die Leute zählen, die "Pencil" oder "Lineal" bestellt haben und der Betrag ist entweder <100 oder >200.
Bitte geben oder kopieren Sie die folgende Formel in eine Zelle und drücken Sie dann Enter Schlüssel, um das Ergebnis zu erhalten, siehe Screenshot:
Note: In der Formel sollten Sie ein Semikolon für die zweite Array-Konstante verwenden, wodurch ein vertikales Array erstellt wird.
Zählen Sie Zellen mit mehreren Sätzen von ODER-Bedingungen mithilfe der SUMPRODUCT-Funktion
Die obige Formel funktioniert nur für zwei Sätze von ODER-Kriterien, wenn Sie mit mehr Kriterien zählen müssen, kann Ihnen eine komplexe SUMMENPRODUKT-Formel zusammen mit ISNUMBER MATCH-Funktionen helfen.
Nehmen Sie zum Beispiel die folgenden Daten, um die Personen zu zählen, die "Pencil" oder "Lineal" bestellt haben und deren Status entweder "Geliefert" oder "In Transit" ist und von "Bob" oder "Eko" unterzeichnet wurde, sollten Sie eine komplexe Formel anwenden .
Bitte kopieren oder geben Sie die folgende Formel in eine leere Zelle ein und drücken Sie dann Enter Taste, um die Berechnung zu erhalten, siehe Screenshot:
Erklärung 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)))
ISNUMMER(MATCH(B2:B13,{"Pencil","Lineal"},0)):
- MATCH(B2:B13,{"Pencil","Lineal"},0): Diese MATCH-Funktion wird verwendet, um jede Zelle im Bereich B2:B13 mit der entsprechenden Array-Konstante zu vergleichen. Wenn die Übereinstimmung gefunden wird, wird eine relative Position des Wertes im Array zurückgegeben, andernfalls wird ein Fehlerwert angezeigt. Sie erhalten also die Array-Liste wie folgt: {1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;# N / A}.
- ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}):Die Funktion ISNUMBER wandelt die Zahlen wie folgt in WAHR und Fehlerwerte in FALSE um: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}.
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))):
- Dann werden diese drei Arrays innerhalb von SUMPRODUCT miteinander multipliziert, wodurch die Werte TRUE und FALSE automatisch in 1 und 0 umgewandelt werden, als Teil der mathematischen Operation wie folgt: SUMPRODUCT({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 SUMPRODUCT-Funktion alle Zahlen im Array, um das Ergebnis zu erhalten: 3.
Verwendete relative Funktion:
- SUMME:
- Die Excel-SUM-Funktion gibt die Summe der gelieferten Werte zurück.
- ZÄHLENWENN:
- Die COUNTIF-Funktion ist eine statistische Funktion in Excel, mit der die Anzahl der Zellen gezählt wird, die ein Kriterium erfüllen.
- SUMMENPRODUKT:
- Die SUMPRODUCT-Funktion kann verwendet werden, um zwei oder mehr Spalten oder Arrays miteinander zu multiplizieren und dann die Summe der Produkte zu erhalten.
- IST NUMMER:
- Die Excel-Funktion ISNUMBER gibt TRUE zurück, wenn eine Zelle eine Zahl enthält, und FALSE, wenn nicht.
- SPIEL:
- Die Microsoft Excel MATCH-Funktion sucht in einem Zellenbereich nach einem bestimmten Wert und gibt die relative Position dieses Werts zurück.
Weitere Artikel:
- Zählen Sie eindeutige numerische Werte basierend auf Kriterien
- Im Excel-Arbeitsblatt kann ein Problem auftreten, bei dem die Anzahl eindeutiger numerischer Werte basierend auf einer bestimmten Bedingung gezählt wird. Wie kann ich beispielsweise die eindeutigen Qty-Werte des Produkts „T-Shirt“ aus dem Bericht zählen, wie unten im Screenshot gezeigt? In diesem Artikel zeige ich einige Formeln, um diese Aufgabe in Excel zu lösen.
- Zählen Sie Zellen, die zwei oder mehr Kriterien entsprechen
- Um in Excel die Anzahl der Zellen anhand von zwei oder mehreren Kriterien zu zählen, kann Ihnen die Funktion ZÄHLENWENN helfen, diese Aufgabe schnell und einfach zu lösen.
- Anzahl der Zeilen mit mehreren ODER-Kriterien zählen
- Um die Anzahl der Zeilen mit mehreren Kriterien in verschiedenen Spalten mit ODER-Logik zu zählen, kann Ihnen die SUMMENPRODUKT-Funktion helfen. Zum Beispiel habe ich einen Produktbericht wie unten gezeigt, jetzt möchte ich die Zeilen zählen, in denen das Produkt "T-Shirt" oder die Farbe "Schwarz" ist. Wie gehe ich mit dieser Aufgabe in Excel um?
Die besten Tools für die Office-Produktivität
Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...
Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).
- Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
- Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
- Erhöht Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
- Bringt effiziente Tabs in Office (einschließlich Excel), genau wie Chrome, Edge und Firefox.
