Direkt zum Inhalt

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

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:

=SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))
  • 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:

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

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:

=SUM(COUNTIF(B2:B13,D2:D3))

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:

=SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))
  • 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:

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

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:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"},C2:C13,{"<100";">200"}))

Hinweis: 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:

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

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.
  • 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 for Excel - Hilft Ihnen, sich von der Masse abzuheben

Beliebte Funktionen: Suchen, markieren oder identifizieren Sie Duplikate  |  Leere Zeilen löschen  |  Kombinieren Sie Spalten oder Zellen, ohne Daten zu verlieren  |  Runde ohne Formel ...
Super VLookup: Mehrere Kriterien  |  Mehrfachwert  |  Über mehrere Blätter hinweg  |  Unscharfe Suche...
Adv. Dropdown-Liste: Einfache Dropdown-Liste  |  Abhängige Dropdown-Liste  |  Mehrfachauswahl Dropdown-Liste...
Spaltenmanager: Fügen Sie eine bestimmte Anzahl von Spalten hinzu  |  Spalten verschieben  |  Schalten Sie den Sichtbarkeitsstatus ausgeblendeter Spalten um  |  Spalten vergleichen mit Wählen Sie Gleiche und Unterschiedliche Zellen ...
Ausgewählte Funktionen: Rasterfokus  |  Designansicht  |  Große Formelleiste  |  Arbeitsmappen- und Blattmanager | Ressourcen (Autotext)  |  Datumsauswahl  |  Arbeitsblätter kombinieren  |  Zellen verschlüsseln/entschlüsseln  |  Senden Sie E-Mails nach Liste  |  Superfilter  |  Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Toolsets:  12 Text Tools (Text hinzufügen, Zeichen entfernen ...)  |  50+ Chart Typen (Gantt-Diagramm ...)  |  40+ Praktisch Formeln (Berechnen Sie das Alter basierend auf dem Geburtstag ...)  |  19 Einfügen Tools (QR-Code einfügen, Bild aus Pfad einfügen ...)  |  12 Umwandlung (Conversion) Tools (Zahlen zu Wörtern, Currency Conversion ...)  |  7 Zusammenführen & Teilen Tools (Erweiterte Zeilen kombinieren, Teilen Sie Excel-Zellen ...)  |  ... und mehr

Kutools for Excel Verfügt über mehr als 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...

Office Tab - Aktivieren Sie das Lesen und Bearbeiten mit 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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations